【DB笔试面试435】SYSTEM和SYSAUX表空间存储的内容有哪些区别？若SYSAUX表空间占用过大则应该如何处理？

在一般情况下，企业产生的业务数据应该存放在单独的数据表空间，而不应该使用系统已存在的表空间，尤其不能将业务数据保存到SYSTEM和SYSAUX表空间中，所以，DBA需要着重关注SYSTEM和SYSAUX表空间的占用情况。

Oracle服务器使用SYSTEM表空间管理整个数据库。这个表空间包含系统的数据字典和关于数据库的管理信息，这些信息均包含在SYS方案中，只有SYS用户或者拥有所需权限的其它管理用户才可访问这些信息。SYSTEM表空间用于核心功能（例如数据字典表）。

SYSAUX是SYSTEM表空间的辅助表空间。Oracle DB早期版本中某些使用SYSTEM表空间或其本身表空间的组件和产品现在改为使用SYSAUX表空间。每个Oracle Database 10g（或更高版本）数据库都必须拥有SYSAUX表空间。辅助表空间SYSAUX用于附加的数据库组件，例如，OEM库（Oracle Enterprise Manager Repository）、AWR快照信息库、统计信息、审计信息等。

SYSTEM和SYSAUX表空间是在创建数据库时创建的必需存在的表空间。这些表空间必须联机。在OPEN状态下，SYSAUX表空间可以脱机以执行表空间恢复，而SYSTEM表空间则不能，这两种表空间都不能设置为只读状态。在MOUNT状态下，任何表空间都可以脱机。

SYSTEM表空间的大小一般变化不大，而SYSAUX表空间在默认条件下如果不做任何配置，那么随着时间的推移，会越来越大。所以，如果SYSAUX表空间过大，那么应该及时诊断清理该表空间。

对于SYSTEM表空间而言，如果占用过大，那么一般情况下是由于审计表（SYS.AUD$）过大引起的。需要将审计表移动到其它表空间中，然后再清理审计表（TRUNCATE TABLE SYS.AUD$）即可。需要注意的是，如果审计表过大，那么应该分步去清理审计表，详细步骤可以参考审计部分，或参考本文末尾内容。

对于SYSAUX表空间而言，如果占用过大，那么一般情况下是由于AWR信息或对象统计信息没有及时清理引起的，具体原因可以通过如下的SQL语句查询：

1SELECT OCCUPANT_NAME "Item",
2       SPACE_USAGE_KBYTES / 1048576 "Space Used (GB)",
3       SCHEMA_NAME "Schema",
4       MOVE_PROCEDURE "Move Procedure"
5  FROM V$SYSAUX_OCCUPANTS
6 WHERE SPACE_USAGE_KBYTES > 1048576
7 ORDER BY "Space Used (GB)" DESC;

如果OCCUPANT_NAME列为SM/AWR（Server Manageability - Automatic Workload Repository），那么表示AWR信息占用过大；如果该列为SM/OPTSTAT（ServerManageability - Optimizer Statistics History），那么表示优化器统计信息占用过大。

也可以直接查询DBA_SEGMENTS视图获取信息：

1SELECT D.SEGMENT_NAME, D.SEGMENT_TYPE,SUM(BYTES)/1024/1024  SIZE_M
2  FROM DBA_SEGMENTS D
3 WHERE D.TABLESPACE_NAME = 'SYSAUX'
4 GROUP BY D.SEGMENT_NAME, D.SEGMENT_TYPE
5 ORDER BY SIZE_M DESC;

然后查询占用空间较大的表，即可得到占用空间较大的原因，下面分别讨论。

（一）AWR信息占用过大

如果确认是AWR信息占用空间过大，那么还可以使用如下的SQL脚本获取AWR占用信息的详细信息：

1sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/awrinfo.sql

如果AWR信息占用过大，那么可以通过设置AWR的保留时间来减小AWR信息的存储空间。通过如下的SQL语句可以获取AWR的保留时间：

1SELECT * FROM DBA_HIST_WR_CONTROL;

通过如下的SQL语句可以设置AWR信息的保留时间为7天（7*24*60），每隔1小时收集一次AWR信息：

1EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>60, RETENTION=>7*24*60);

需要注意的是，在Oracle 10g中，AWR默认保留7天，在Oracle 11g中，AWR默认保留8天。

在以上设置完成后，可以删除不需要的AWR快照信息，从而释放SYSAUX表空间，相关SQL语句如下所示：

1SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM DBA_HIST_SNAPSHOT;
2SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM DBA_HIST_ACTIVE_SESS_HISTORY;
3BEGIN 
4     DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE( 
5      LOW_SNAP_ID => 1, 
6      HIGH_SNAP_ID => 36768, 
7      DBID => 1148453265); 
8END; 

如果DBM����,����S_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE执行太慢，那么可以先执行TRUNCATE操作：

1select distinct 'truncate  table  '||segment_name||';',s.bytes/1024/1024
2  from dba_segments s
3 where s.segment_name like 'WRH$%'
4   and segment_type in ('TABLE PARTITION', 'TABLE')
5   and s.bytes/1024/1024>100
6   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操作，例如：

1ALTER TABLE WRH$_ACTIVE_SESSION_HISTORY MOVE PARTITION 分区名称;

执行完MOVE操作后，需要对索引进行重建。同理，对于分区索引，只能对分区的单个索引进行重建，而不能总体重建：

1ALTER INDEX WRH$_ACTIVE_SESSION_HISTORY_PK REBUILD PARTITION 分区名称;

需要注意的是，可以在以上SQL后加上“UPDATE GLOBALINDEXES”子句让全局索引不失效。

（二）统计信息占用过大

如果统计信息占用空间过大，那么可以修改统计信息的保留时间。统计信息默认保留31天，过期的统计信息会自动被删除。

1SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL;  --查询统计信息的保留时间
2EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(7);  --设置统计信息的保留时间

若发现统计信息占用了SYSAUX上的大量空间，则可以考虑使用DBMS_STATS.PURGE_STATS过程实施清理。

以下的SQL语句对于诊断SYSAUX表空间的占用情况非常有用：

 1SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM DUAL;
 2SELECT MIN(SAVTIME), MAX(SAVTIME) FROM WRI$_OPTSTAT_TAB_HISTORY;
 3SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_IND_HISTORY;
 4SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
 5SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
 6SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_AUX_HISTORY;
 7SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_TAB_HISTORY;
 8SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_IND_HISTORY;
 9SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
10SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
11SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_AUX_HISTORY;
12SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_OPR;

以下SQL可以查询到无效的ASH信息：

1SELECT COUNT(*)
2  FROM SYS.WRH$_ACTIVE_SESSION_HISTORY A
3 WHERE NOT EXISTS (SELECT 1
4          FROM SYS.WRM$_SNAPSHOT B
5         WHERE A.SNAP_ID = B.SNAP_ID
6           AND A.DBID = B.DBID
7           AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER);

最后需要说明的一点是，负责收集和清理AWR信息的后台进程为MMON，而隐含参数“_swrf_test_action”可以调试MMON的行为，可以和10046事件结合使用。MMON进程每分钟都会自动刷新一定的AWR数据到磁盘上，默认情况下，MMON每30分钟做一次AWR信息的清理工作。在trace文件中可以看到“MMON Auto-Purge cycle”字样。

& 说明：

有关SYSTEM和SYSAUX的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2152868/

有关审计的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2140644/

有关数据库操作导致索引失效的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2152871/

在日常的数据库维护中，经常出现SYSTEM表空间被撑满，在绝大多数情况下是因为数据库登录审计的功能被启动了，此时一般建议把SYS.AUD$相关对象迁移到其它表空间，从而避免SYSTEM被用完的风险。

在Oracle 11g之前迁移方法如下所示：

1ALTER TABLE SYS.AUDIT$ MOVE TABLESPACE USERS;
2ALTER TABLE SYS.AUDIT_ACTIONS MOVE TABLESPACE USERS;
3ALTER TABLE SYS.AUD$ MOVE TABLESPACE USERS;
4ALTER TABLE SYS.AUD$ MOVE LOB(SQLBIND) STORE AS SYS_IL0000000384C00041$$ (TABLESPACE USERS);
5ALTER TABLE SYS.AUD$ MOVE LOB(SQLTEXT) STORE AS SYS_IL0000000384C00041$$ (TABLESPACE USERS);
6ALTER INDEX SYS.I_AUDIT REBUILD ONLINE TABLESPACE USERS;
7ALTER INDEX SYS.I_AUDIT_ACTIONS REBUILD ONLINE TABLESPACE USERS;

从Oracle 11g开始可以使用DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION进行迁移：

1EXEC DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(AUDIT_TRAIL_TYPE=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,AUDIT_TRAIL_LOCATION_VALUE => 'USERS');

如下示例为授予审计的一些权限：

1GRANT AUDIT ANY TO LHR_TEST;
2GRANT AUDIT SYSTEM TO LHR_TEST;

在Oracle 11g之前通过手工清理的方式或自定义作业来定期清理SYS.AUD$表，如下：

1TRUNCATE TABLE SYS.AUD$;
2DELETE FROM SYS.AUD$ WHERE OBJ$NAME='EMP';

需要注意的是，如果AUD$表过大，那么直接TRUNCATE AUD$表，系统要立即释放大量的EXTENTS，会严重影响系统性能。可以通过如下2个步骤逐步释放EXTENTS：

①　清空数据并且保留原来的EXTENTS：

1TRUNCATE TABLE SYS.AUD$ REUSE STORAGE;

在这里，REUSE STORAGE是TRUNCATE的一个参数，表示保持原来的存储不变。一般情况下，SQL命令“TRUNCATE TABLE TABLE_NAME;”其实就是“TRUNCATE TABLE TABLE_NAME DROP STORAGE;”。DROP STORAGE是TRUNCATE TABLE的默认参数。

②　逐步回缩EXTENTS：

1ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 5000M;
2ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 2000M;
3……
4ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 10M;

需要注意的是，在执行的时候，可以根据实际情况调整每次回缩空间的大小。

若审计在OS和XML选项下进行手动删除审计文件。在Oracle 11g中通过DBMS_AUDIT_MGMT包下的子过程进行手动或定期清理。下面的过程可以迁移审计记录到USERS表空间：

1conn / as sysdba
2BEGIN
3  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(AUDIT_TRAIL_TYPE           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
4                                           AUDIT_TRAIL_LOCATION_VALUE => 'USERS');
5
6END;
7/

使用包DBMS_AUDIT_MGMT下的INIT_CLEANUP过程可以设置审计的清除间隔，还有很多其它的实用存储过程请参考官方文档。

& 说明：

有关审计的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2140644/