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_tables
where 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);
原文地址：https://blog.csdn.net/weixin_52444902/article/details/143056095
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！