oracle system信息统计,Oracle的SYSTEM和SYSAUX表空间 转载：https://blog.csdn.net/weixin_33724759/article/details/116294716
一般情况下，业务数据应该存放在单独的数据表空间，而不应该使用系统已存在的表空间，尤其不能将业务数据保存到SYSTEM和SYSAUX表空间中，所以，DBA需要着重关注SYSTEM和SYSAUX表空间的占用情况。
Oracle服务器使用SYSTEM表空间管理整个数据库。这个表空间包含系统的数据字典和关于数据库的管理信息，这些信息均包含在SYS SCHEMA中，只有SYS用户或者拥有所需权限的其它管理用户才可访问这些信息。SYSTEM表空间用于核心功能(例如数据字典表)，审计信息表AUD$默认也在SYSTEM表空间。
SYSAUX是SYSTEM表空间的辅助表空间。Oracle DB早期版本中某些使用SYSTEM表空间或其本身表空间的组件和产品现在改为使用SYSAUX表空间。Oracle Database 10g(或更高版本)数据库都必须拥有SYSAUX表空间。SYSAUX用于附加的数据库组件，例如，OEM库(Oracle Enterprise ManagerRepository)、AWR快照信息库、统计信息等。
SYSTEM和SYSAUX表空间是在创建数据库时创建的必须存在的表空间。表空间必须联机。在OPEN状态下，SYSAUX表空间可以脱机以执行表空间恢复，而SYSTEM表空间则不能，这两种表空间都不能设置为只读状态。在MOUNT状态下，表空间都可以脱机。
经历过SYSTEM和SYSAUX表空间过大的四种情况：
###1、SYSTEM表空间过大(审计导致)
在oracle11g中，数据库的审计功能是默认开启的(这和oracle10g的不一样，10g默认是关闭的)，如果不需要审计功能创建库时最好直接关闭。
audit_trail 参数的值可以设置为以下几种
. NONE：不开启
. DB：开启审计功能
. OS：审计记录写入一个操作系统文件。
. TRUE：与参数DB一样
. FALSE：不开启审计功能。
这个参数静态参数，关闭和开启需要重启数据库
SQL> alter system set audit_trail=none scope=spfile;
SQL> showdown immediate;
SQL> startup
查看是否审计功能是否启动
SQL> show parameter audit_trail
如果已经开启审计功能，后续还继续使用审计功能，需要将审计表移动到其它表空间中，然后再清理审计表(TRUNCATE TABLE SYS.AUD$)或者采取dbms_audit_mgmt来清除即可，不建议使用delete，不释放空间。
注：清理之前确定审计是否需要保留，如需要，建立临时表导出。
create table audit_bak tablespace tablespace_name as select * from sys.aud$;
exp/expdp导出备份move到其他表空间操作
sql>alter table aud$ move tablespace;
sql>alter index I_aud1 rebuild online tablespace ;
SQL>alter table audit$ move tablespace;
SQL>alter index i_audit rebuild online tablespace ;
SQL>alter table audit_actions move tablespace ;
SQL>alter index i_audit_actions rebuild online tablespace ;
###2、SYSTEM表空间过大(大量删除表导致)
业务使用常规表作为临时表使用，每次使用完成后删除表未添加purge，已开启回收站并但一直未做清理，累积时间过长，导致回收站数据表数量巨大(五百万张表)，数据字典表C_OBJ#、C_COBJ#等占用空间增加，从而使SYSTEM表空间增长。
注：负责的DBA居然增加了2个数据文件，负责巡检的第三方也没有发现表空间增加到80G，其实此问题应该早就被发现。
解决办法：临时关闭回收站alter system set recyclebin=off;删除表代码增加purge
创建存储过程定时清理回收站(purge recyclebin; 2小时才清理2w个左右)
更改代码使用临时表
清理完成，更改代码完成，打开回收站alter system set recyclebin=on
###3、SYSAUX表空间过大(AWR信息导致)
查询SYSAUX表空间中占用空间最多的组件和对象
select OCCUPANT_NAME,OCCUPANT_DESC,SPACE_USAGE_KBYTES/1024 USAGE_MB
from V$SYSAUX_OCCUPANTS
order by SPACE_USAGE_KBYTES DESC;
SELECT D.SEGMENT_NAME, D.SEGMENT_TYPE,SUM(BYTES)/1024/1024 SIZE_MB
FROM DBA_SEGMENTS D
WHERE D.TABLESPACE_NAME = 'SYSAUX'
GROUP BY D.SEGMENT_NAME, D.SEGMENT_TYPE
ORDER BY SIZE_MB DESC;
如果确认是AWR信息占用空间过大，那么还可以使用如下的SQL脚本获取AWR占用信息的详细信息：sqlplus as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/awrinfo.sql
可以通过设置AWR的保留时间来减小信息的存储空间。
获取AWR的保留时间：
SELECT * FROM DBA_HIST_WR_CONTROL;
可以设置AWR信息的保留时间为7天(7*24*60)，每隔1小时收集一次AWR信息：(一般性能要求较高系统，建议收集时间缩短)
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>60,RETENTION=>7*24*60);
在Oracle 10g中，AWR默认保留7天，在Oracle 11g中，AWR默认保留8天。
设置完成后，删除不需要的AWR快照信息，从而释放SYSAUX表空间：
SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROMDBA_HIST_SNAPSHOT;
SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROMDBA_HIST_ACTIVE_SESS_HISTORY;
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
LOW_SNAP_ID => 66666, ==>起始快照id
HIGH_SNAP_ID => 88888, ==>结束快照id
DBID=> 1343683905); ==>oracle的DBID
END;
如果DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE执行比较慢，那么可以先执行TRUNCATE操作，下边SQL生成TRUNCATE脚本：
select distinct 'truncate table '||segment_name||';',s.bytes/1024/1024
from dba_segments s
where s.segment_name like'WRH$%'
and segment_type in('TABLE PARTITION', 'TABLE')
and s.bytes/1024/1024>100
order by s.bytes/1024/1024/1024 desc;
TRUNCATE操作后，再执行
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE。
需要注意的是，TRUNCATE操作会将AWR中的所有信息全部清除。所以，需要先确认释放需要这些AWR信息，当然也可以先把需要的AWR信息做导出操作，然后再清空以上AWR信息。
注:DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE是通过DELETE操作来完全清理工作的。所以，执行完成后，并不会真正的释放SYSAUX表空间。应该对相关的表执行MOVE或TRUNCATE操作,在执行MOVE操作时，由于AWR信息的表都是分区表，不能对分区表全表执行MOVE操作，所以需要单独对分区执行MOVE操作，
例：ALTER TABLE WRH$_ACTIVE_SESSION_HISTORY MOVE PARTITION PARTIRION_NAME(分区名称);
执行完MOVE操作后，需要对索引进行重建。对于分区本地索引，也只能对分区的单个索引进行重建，而不能总体重建：ALTER INDEX WRH$_ACTIVE_SESSION_HISTORY_PK REBUILD PARTITION PARTIRION_NAME(分区名称);
添加“UPDATE GLOBAL INDEXES”可以使全局索引不失效。
###4、SYSAUX表空间过大(统计信息导致)
SM/OPTSTAT是用于存储老的统计信息，10G之前，当对表/字段/索引做了相应的统计信息之后，新的统计信息就会覆盖老的统计信息，也就是说的无法直接找回统计信息，要找回，只能事先通过dbms_stats导出来。10G就不必了，它会自动的存到相应的表里，而这些表是存在sysaux的，但这也引出了一个问题：如果这些表的数据不断的增长，而不把老的数据删除的话，sysaux迟早会被撑爆。 默认的情况下,系统会为SM/OPTSTAT保留31天的记录，可以通过dbms_stats.get_stats_history_retention 来确定。这里的统计信息跟AWR是有区别的, AWR默认保留7天. SM/OPTSTAT保留的时间可以通过dbms_stats.alter_stats_history_retention来控制。如果SM/OPTSTAT确实占用了比较多的空间，要删除某个时间前的记录可以用：dbms_stats.purge_stats. 这个SP其实只从存储历史统计信息的表里删除记录，这样的话，就会出现一种情况，删除了大量的数据，但这些表占用的空间并没有释放,也就是HWM不会降下来的。这时要手工处理。
解决方法：purge(底层用delete删除)
查看当时历史数据
select dbms_stats.get_stats_history_availability from dual;
假设历史是200天，循环清理15天以前的。
begin
for i in reverse 15..200
loop
dbms_stats.purge_stats(sysdate-i);
end loop;
end;
/
然后再查询当前历史数据
select dbms_stats.get_stats_history_availability from dual;TRUNCATE
只有当purge的情况下，还是没有释放空间，或者需删除量过大时，才建议用truncate方式进行清理。
备份准备删除基表中最新数据
create table SYS.WRI$_OPTSTATHISTHEADHIS_BAK as (select * from sys. WRI$_OPTSTAT_HISHEAD_HISTORY where savtime > SYSDATE - 14);
create table SYS.WRI$_OPTSTATHISTGRMHIS_BAK as (select * fromsys.WRI$_OPTSTAT_HISTGRM_HISTORY where savtime > SYSDATE - 14);
create table SYS.WRI$_OPTSTAT_TAB_HIS_BAK as (select * from sys.WRI$_OPTSTAT_TAB_HISTORY where savtime > SYSDATE - 14);
create table SYS.WRI$_OPTSTAT_IND_HIS_BAK as (select * from sys.WRI$_OPTSTAT_IDX_HISTORY where savtime > SYSDATE - 14);
查看SM/OPTSTAT(用于存储老的统计信息)保留天数
select dbms_stats.get_stats_history_retention from dual;
设置SM/OPTSTAT保留的时间(-1表示无限)exec dbms_stats.alter_stats_history_retention(-1);
truncate较大的TABLE
truncate table sys.WRI$_OPTSTAT_HISTHEAD_HISTORY;
truncate table sys.WRI$_OPTSTAT_HISTGRM_HISTORY;
truncate table sys.WRI$_OPTSTAT_TAB_HISTORY;
truncate table sys.WRI$_OPTSTAT_IDX_HISTORY;
清理历史统计信息
exec dbms_stats.purge_stats(sysdate-200);
exec dbms_stats.purge_stats(sysdate-100);
exec dbms_stats.purge_stats(sysdate-50);
exec dbms_stats.purge_stats(sysdate-15);
将历史统计信息保留时间设为15天
exec dbms_stats.alter_stats_history_retention(15);
select dbms_stats.get_stats_history_retention from dual;
将历史统计信息相关的表进行MOVE并在线重建索引。
alter table sys.WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace sysaux;
alter index sys.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST rebuild online;
alter index sys.I_WRI$_OPTSTAT_HH_ST rebuild online;
...
alter table sys.WRI$_OPTSTAT_IND_HISTORY move tablespace sysaux;
alter index sys.I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild online;
alter index sys.I_WRI$_OPTSTAT_IND_ST rebuild online;
对MOVE表的统计信息进行收集
EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname =>'WRI$_OPTSTAT_HISTGRM_HISTORY',cascade => TRUE);
...
EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname =>'WRI$_OPTSTAT_HISTHEAD_HISTORY',cascade => TRUE);
