sysaux表空间清理

1,查看sysaux表空间使用情况
COL Item FOR A30
COL Schema FOR A30
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;

2,查询SYSAUX表空间中具体哪个表占用率大：
select * from (
select segment_name,SEGMENT_TYPE,sum(bytes)/1024/1024 total_mb from dba_segments where tablespace_name =
'SYSAUX' group by segment_name,SEGMENT_TYPE order by 3 desc)
where rownum <=20;

3，查询快照信息：
SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM DBA_HIST_SNAPSHOT;

4, 创建临时表，保存近8天的数据
create table readtest.ash_bak_202111091 as select * from sys.WRH$_ACTIVE_SESSION_HISTORY where SAMPLE_TIME>=sysdate-9;
SQL> select count(*) from readtest.ash_bak_20211109;
COUNT(*)
----------
12648
1 row selected.

SQL> select count(*) from sys.WRH$_ACTIVE_SESSION_HISTORY;


5, 禁用AWR
exec dbms_workload_repository.modify_snapshot_settings(interval => 0);

6, truncate WRH$_ACTIVE_SESSION_HISTORY
TRUNCATE TABLE sys.WRH$_ACTIVE_SESSION_HISTORY;

7, truncate 完成后，重建 WRH$_ACTIVE_SESSION_HISTORY表的索引，并从备份表恢复数据
insert into sys.WRH$_ACTIVE_SESSION_HISTORY select * from readtest.ash_bak_20211109;
commit;

8, 查看索引状态
set line 300 pages 200
col owner for a20
col index_name for a30
select index_owner,
index_name,
partition_name,
status,
tablespace_name,
last_analyzed
from dba_ind_partitions
where index_name in (select index_name
from dba_indexes
where table_name in ('WRH$_ACTIVE_SESSION_HISTORY')
and table_owner = 'SYS');

9, 如果索引失效重建索引（测试的时候是没有失效的，索引为本地索引）
alter index sys.WRH$_ACTIVE_SESSION_HISTORY_PK rebuild parallel 8 nologging;
alter index sys.WRH$_ACTIVE_SESSION_HISTORY_PK noparallel;

10, 启动AWR
exec dbms_workload_repository.modify_snapshot_settings(interval => 60);

11, 测试awr和ash可能正常生成
---手动生成snapshot
exec dbms_workload_repository.create_snapshot;
@?/rdbms/admin/awrrpt
@?/rdbms/admin/ashrpt

12, 删除备份表
drop table readtest.ash_bak_20211109 purge;