Sysaux】sysaux表空间清理,小心有坑
- SYS@orcl1 > set lines 200
- SYS@orcl1 > Select Tablespace_Name,
- Sum_m,
- Max_m,
- Count_Blocks Free_Blk_Cnt,
- Sum_Free_m,
- To_Char(100 * Sum_Free_m / Sum_m, '99.9999')|| '%' As Pct_Free,
- 100 - To_Char(100 * Sum_Free_m / Sum_m,'99.9999') || '%' As Pct_used
- From (Select Tablespace_Name, Sum(Bytes) / 1024 / 1024 As Sum_m
- From Dba_Data_Files
- Group By Tablespace_Name)
- Left Join
- (Select Tablespace_Name As Fs_Ts_Name,
- Max(Bytes) / 1024 / 1024 As Max_m,
- Count(Blocks) As Count_Blocks,
- Sum(Bytes / 1024 / 1024) As Sum_Free_m
- From Dba_Free_Space
- Group By Tablespace_Name)
- On Tablespace_Name = Fs_Ts_Name
- ORDER BY Sum_Free_m / Sum_m ;
- col Item for a30
- col Schema for a20
- set lines 200
- SYS@orcl1 > SELECT occupant_name"Item",
- round(space_usage_kbytes/1024/1024,3)"Space Used (GB)",
- schema_name "Schema",
- move_procedure "MoveProcedure"
- FROM v$sysaux_occupants
- ORDER BY 2 Desc;
- SYS@orcl1 > col SNAP_INTERVAL for a40
- SYS@orcl1 > col RETENTION for a30
- SYS@orcl1 > select * from dba_hist_wr_control;
- DBID SNAP_INTERVAL RETENTION TOPNSQL
- ---------- -------------------- --------------------------------------------------------------------------- ----------
- 1361686490 +00000 00:30:00.0 +00008 00:00:00.0 DEFAULT
- SYS@orcl1 > begin
- 2 dbms_workload_repository.modify_snapshot_settings(
- 3 interval => 60
- 4 );
- 5 end;
- 6 /
- PL/SQL procedure successfully completed.
- SYS@orcl1 > col SNAP_INTERVAL for a40
- SYS@orcl1 > col RETENTION for a30
- SYS@orcl1 > select * from dba_hist_wr_control;
- DBID SNAP_INTERVAL RETENTION TOPNSQL
- ---------- -------------------------------------------------------------------------------------------- ----------
- 1361686490 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT
- SYS@orcl1 > select min(snap_id),max(snap_id) from dba_hist_snapshot;
- MIN(SNAP_ID) MAX(SNAP_ID)
- ------------ ------------
- 37091 37680
- Syntax:
- DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
- low_snap_id IN NUMBER,
- high_snap_id IN NUMBER
- dbid IN NUMBER DEFAULT NULL);
- Examples:
- EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(37091, 37679);
- SYS@orcl1 > select min(snap_id),max(snap_id) from dba_hist_snapshot;
- MIN(SNAP_ID) MAX(SNAP_ID)
- ------------ ------------
- 37091 37680
- select * from (select segment_name,PARTITION_NAME,segment_type,bytes/1024/1024 from dba_segments where tablespace_name='SYSAUX' order by 4 desc) where rownum<=10;
3.查表基本的组织结构发现WRH$表中都有snap_id字段,所以我们就用这个字段进行分界线处理。我们对占用空间第一的表进行处理,备份WRH$_ACTIVE_SESSION_HISTORY表保留数据到WRH$_ACTIVE_SESSION_HISTORY_B表。
- CREATE TABLE WRH$_ACTIVE_SESSION_HISTORY_B AS SELECT * FROM WRH$_ACTIVE_SESSION_HISTORY WHERE SNAP_ID>37679 ;
- SELECT COUNT(*) FROM WRH$_ACTIVE_SESSION_HISTORY_B;
- TRUNCATE TABLE WRH$_ACTIVE_SESSION_HISTORY;
- INSERT INTO WRH$_ACTIVE_SESSION_HISTORY SELECT * FROM WRH$_ACTIVE_SESSION_HISTORY_B;
- COMMIT;
- SELECT COUNT(*) FROM WRH$_ACTIVE_SESSION_HISTORY;
- drop table WRH$_ACTIVE_SESSION_HISTORY_B purge;