Oracle数据库清理和回收system和sysaux表空间
1.先查询下SYSTEM和SYSAUX表空间的使用率
--查询下SYSTEM和SYSAUX表空间的使用率 SELECT * FROM ( SELECT D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)", BLOCKS "SUM_BLOCKS", SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)", ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "USED_RATE(%)", FREE_SPACE || 'M' "FREE_SPACE(M)" FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, SUM (BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) UNION ALL SELECT D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)", BLOCKS SUM_BLOCKS, USED_SPACE || 'M' "USED_SPACE(M)", ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)", NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)" FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, SUM (BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ORDER BY 1) WHERE TABLESPACE_NAME IN ('SYSAUX','SYSTEM'); --end
2.查询哪些表占用空间较大
--查询哪些表占用空间较大 select * from ( select segment_name,sum(bytes)/1024/1024 total_mb,tablespace_name from dba_segments where tablespace_name in ('SYSTEM','SYSAUX') group by segment_name,tablespace_name order by 2 desc) where rownum <=20; --end
3.查看下SYSAUX表空间的使用情况
--查看下SYSAUX表空间的使用情况，可以通过v$sysaux_occupants视图查询到 SELECT occupant_name "Item", space_usage_kbytes / 1048576 "Space Used (GB)", schema_name "Schema", move_procedure "Move Procedure" FROM v$sysaux_occupants ORDER BY 2 desc; --end SELECT occupant_name "Item", space_usage_kbytes / 1048576 "Space Used (GB)", schema_name "Schema", move_procedure "Move Procedure" FROM v$sysaux_occupants where occupant_name='SM/AWR'; --end
4.通过查看v$sysaux_occupants视图，可以确定占用SYSAUX表空间过多的大部分都是AWR的基表，这样只要删除部分AWR数据理论上就可以回收一部分SYSAUX表空间，
通常AWR的数据都会设置保留期限，10g版本默认保留7天，11g版本默认保留8天，可以通过dba_hist_wr_control视图来查看(注：并不是所有DBA开头的表都是数据字典，
也有很多是视图，dba_hist_wr_control就是视图)。
--查看AWR数据保留期限
select * from dba_hist_wr_control;
AWR的数据既然只保留七八天，为什么还会占用这么多的SYSAUX表空间呢？这个问题我个人认为主要有以下两个原因，
首先，AWR删除过期的数据是通过DELETE操作完成的，这样就会产生大量的碎片，特别是SYSAUX表空间存在自动扩展的数据文件，
而且这个数据文件没有扩展到最大，还有扩展的空间情况下会很明显，其次就是ASH的数据有些情况下是不受AWR的保留策略影响的，
这个从下面的SQL就可以看出。
--查看HISTORY表的数据量
select count(*) from SYS.WRH$_ACTIVE_SESSION_HISTORY;
count(*)
-------------
13381288
--查看ASH快照id数量
select min(snap_id),max(snap_id) from sys.wrh$_active_session_history;
--查看下AWR统计数的保存天数
select dbms_stats.get_stats_history_retention from dual;
5.再次查看
--查看AWR占用SYSAUX表空间的大小 SELECT occupant_name "Item", space_usage_kbytes / 1048576 "Space Used (GB)", schema_name "Schema", move_procedure "Move Procedure" FROM v$sysaux_occupants where occupant_name='SM/AWR'; --end
6. 清空WRH$_ACTIVE_SESSION_HISTORY 表
--查看 WRH$_ACTIVE_SESSION_HISTORY 各个分区表大小
SELECT owner,
segment_name,
partition_name,
segment_type,
bytes /1024/1024 Size_M
FROM dba_segments
WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY'
order by 2;
--生成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 ,在sqlplus下执行
truncate table WRH$_ACTIVE_SESSION_HISTORY;
done
参考：
https://www.cnblogs.com/chxmtl/p/11981862.html
http://www.dbdream.com.cn/2017/01/oracle%e6%95%b0%e6%8d%ae%e5%ba%93%e6%b8%85%e7%90%86%e5%92%8c%e5%9b%9e%e6%94%b6system%e5%92%8csysaux%e8%a1%a8%e7%a9%ba%e9%97%b4/
----------------------------------------------------------------------
下面的操作是snap_id 从1开始时的终极办法，上面的能搞定就行了
-
6. 通过MOVE操作回收这个表的水位线，来回收这部分被删除数据占用的空间。这个表是分区表，分区表不支持表级别的MOVE操作，
直接对分区表进行MOVE操作会遇到ORA-14511错误。
alter table WRH$_ACTIVE_SESSION_HISTORY move;
ERROR at line 1:
ORA-14511: cannot perform operation on a partitioned object
--查看这个表的分区信息，有多个分区
select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name='WRH$_ACTIVE_SESSION_HISTORY';
1 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_827670780_58432 0.3671875
2 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_827670780_58456 0.3046875
3 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_827670780_58480 0.3359375
4 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_827670780_58504 0.34375
5 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_827670780_58528 0.390625
6 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_827670780_58552 0.375
7 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_827670780_58576 0.34375
8 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_827670780_58600 0.3203125
9 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_827670780_58624 0.1953125
10 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN 6.103515625E-5
7.下面按照分区进行MOVE操作，来回收空间
alter table WRH$_ACTIVE_SESSION_HISTORY move partition WRH$_ACTIVE_1357933872_0;
alter table WRH$_ACTIVE_SESSION_HISTORY move partition WRH$_ACTIVE_SES_MXDB_MXSN;
8.对分区表进行MOVE之后，需要重建索引，查看这个表的索引信息
select index_name from dba_indexes where table_name='WRH$_ACTIVE_SESSION_HISTORY';
----------------------------------------------------
INDEX_NAME
1 WRH$_ACTIVE_SESSION_HISTORY_PK
这个表只有一个主键，而且是分区索引，也不能对分区索引直接进行REBUILD操作，否则会遇到ORA-14086错误。
alter index WRH$_ACTIVE_SESSION_HISTORY_PK rebuild;
ERROR at line 1:
5
ORA-14086: a partitioned index may not be rebuilt as a whole
9.下面查看下这个索引的分区信息
select partition_name from dba_ind_partitions where index_name='WRH$_ACTIVE_SESSION_HISTORY_PK';
--------------------------------------------------
PARTITION_NAME
1 WRH$_ACTIVE_SES_MXDB_MXSN
2 WRH$_ACTIVE_827670780_58504
3 WRH$_ACTIVE_827670780_58576
4 WRH$_ACTIVE_827670780_58552
5 WRH$_ACTIVE_827670780_58528
6 WRH$_ACTIVE_827670780_58432
7 WRH$_ACTIVE_827670780_58600
8 WRH$_ACTIVE_827670780_58624
9 WRH$_ACTIVE_827670780_58480
10 WRH$_ACTIVE_827670780_58456
10.然后按照分区进行重建索引
alter index WRH$_ACTIVE_SESSION_HISTORY_PK rebuild partition WRH$_ACTIVE_1357933872_0;
alter index WRH$_ACTIVE_SESSION_HISTORY_PK rebuild partition WRH$_ACTIVE_SES_MXDB_MXSN;
11.再次查看，WRH$_ACTIVE_SESSION_HISTORY的空间已经回收
select sum(bytes)/1024/1024 from dba_segments where segment_name='WRH$_ACTIVE_SESSION_HISTORY';
select sum(bytes)/1024/1024 from dba_segments where segment_name='WRH$_ACTIVE_SESSION_HISTORY_PK';
附：
trancate 表空间不释放的解决办法
1.
--检查T_IMP_TRUNCATE表的 INITIAL_EXTENT参数将近 100MB
SELECT T.TABLE_NAME, T.INITIAL_EXTENT/1024/1024 mb
FROM USER_TABLES T
WHERE T.TABLE_NAME = 'T_IMP_TRUNCATE'
2.决定对T_IMP_TRUNCATE表进行收缩shrink
--a.启用行迁移
alter table T_IMP_TRUNCATE enable row movement;
--b.shrink表T_IMP_TRUNCATE
alter TABLE T_IMP_TRUNCATE shrink SPACE;
--c.关闭行迁移
alter table T_IMP_TRUNCATE DISABLE row movement;
3.shrink后T_IMP_TRUNCATE表空间已释放
SELECT SUM(T.BYTES) / 1024 / 1024 MB
FROM USER_SEGMENTS T
WHERE T.SEGMENT_NAME = 'T_IMP_TRUNCATE';
