Oracle数据库系统表空间过大，清理SYSTEM、SYSAUX表空间

一.前言

在oracle 数据库中，system为系统表空间，存放着一些我们经常用到的系统表和视图，sysaux为辅助表空间，辅助着系统表空间。这两个表空间不宜添加数据文件，会使系统表空间过于臃肿，从而影响数据库的使用。本文讲解如何正确清理这两个表空间内的数据。

二.SYSTEM表空间清理

system表空间存放这重要的系统表的信息，所以对于system表空间的操作要格外小心，要知道哪些可以清理，哪些不能清理。通常system表空间使用率较高都是因为数据库开启了审计日志，审计日志日益增大导致system表空间使用率较高。
查看system表空间占用空间比较大的对象这里找出了大于100mb的所有对象

select * from (select SEGMENT_NAME,sum(bytes)/1024/1024 sx from dba_segments
where tablespace_name='SYSTEM' group by segment_name)
where sx>100 order by sx desc;

在这里插入图片描述
查询结果发现AUD这个对象占用了大量的空间，AUD表是一个审计表。从11gr2版本开始，oracle把参数audit_trail自动设置为DB级别，导致很多数据库的操作被记录在审计表sys.aud中，造成sys.aud所在的表空间快速增长。可以直接使用truncate此表，释放空间。审计日志表的清理不会影响数据库的正常运行。

truncate table sys.aud$;

也可以关闭数据库的审计日志
-关闭审计（需重启数据库）

alter system set audit_trail=none scope=spfile;
showdown immediate;
startup;

或者把AUD$表移动到其他表空间
检查审计跟踪表的当前表空间

SQL> SELECT table_name, tablespace_name FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name; TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
AUD$                      		    SYSTEM
FGA_LOG$                   	        SYSTEM

检查当前两个表的大小

select segment_name,bytes/1024/1024 size_in_megabytes from dba_segments where segment_name in ('AUD$','FGA_LOG$');SEGMENT_NAME               SIZE_IN_MEGABYTES
-------------------  -----------------------------
AUD$             			       12
FGA_LOG$            	           .0625

使用过程DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION移动审计跟踪表这里是移动到SYSAUX表空间也可以新建表空间

SQL> BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,--this moves table AUD$
audit_trail_location_value => 'SYSAUX');
END;
/
PL/SQL procedure successfully completed.SQL> BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,--this moves table FGA_LOG$
audit_trail_location_value => 'SYSAUX');
END;
/
PL/SQL procedure successfully completed.

检查表是否成功移动

SQL>select table_name,tablespace_name from dba_tableswhere table_name in ('AUD$','FGA_LOG$') order by table_name;TABLE_NAME              TABLESPACE_NAME
----------------------- ------------------------------
AUD$                    	 SYSAUX
FGA_LOG$                 	 SYSAUX

三.SYSAUX表空间清理

SYSAUX作为SYSTEM的辅助表空间记录了AWR快照信息库、统计信息、审计信息等

查看SYSAUX表空间占用空间比较大的对象查找出占用比较大的对象名都为WRH$开头都为awr信息

select * from (select SEGMENT_NAME,sum(bytes)/1024/1024/1024 sx from dba_segments
where tablespace_name='SYSAUX' group by segment_name)
where sx>1 order by sx desc;

AWR报告默认是采取DELETE的方式进行过期信息删除的，相比TRUNCATE而言，就会产生大量的碎片，对于开启了自动扩展数据文件的表空间而言，碎片的现会象更加严重。
根据用DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE来删除快照耗时非常久,生产库这样子操作很危险，在执行该过程时后台实际运行的都是delete基表的动作,会导致归档日志切换频繁,产生大量归档从而导致归档目录空间不足.也很消耗undo,导致undo表空间不足.如果需要清理的快照信息不多,DROP_SNAPSHOT_RANGE是首要选择。如果需要清理的比较多，那么最好是先truncate后再通过包进行清理。

查看最大和最小快照号

SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM DBA_HIST_SNAPSHOT; 
SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM DBA_HIST_ACTIVE_SESS_HISTORY;

进行truncate 如果需要保留可以创建一个中间表将需要保留的导入到中间表中 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包清理快照信息下面两个id写之前查到的快照id 如果不truncate直接通过包清理

EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(64921, 65641);