oracle 11g SYSAUX表空间清理
oracle 11g SYSAUX表空间清理
SELECT OCCUPANT_NAME "Item",SPACE_USAGE_KBYTES / 1048576 "Space Used (GB)",SCHEMA_NAME "Schema",MOVE_PROCEDURE "Move Procedure"FROM V$SYSAUX_OCCUPANTS WHERE SPACE_USAGE_KBYTES > 1048576 ORDER BY "Space Used (GB)" DESC;
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;
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE BYTES/1024/1024
----------------------------------- ------------------------------------------------------------ ------------------------------------ ---------------
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2248810413_0 TABLE PARTITION 8083
SYS_LOB0000006409C00004$$ LOBSEGMENT 2112
WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_2248810413_0 INDEX PARTITION 864
WRH$_EVENT_HISTOGRAM_PK WRH$_EVENT__2248810413_0 INDEX PARTITION 448
WRH$_EVENT_HISTOGRAM WRH$_EVENT__2248810413_0 TABLE PARTITION 429
WRH$_LATCH WRH$_LATCH_2248810413_0 TABLE PARTITION 312
WRH$_SQLSTAT WRH$_SQLSTA_2248810413_0 TABLE PARTITION 305
WRH$_SERVICE_STAT_PK WRH$_SERVIC_2248810413_0 INDEX PARTITION 240
WRH$_SYSSTAT_PK WRH$_SYSSTA_2248810413_0 INDEX PARTITION 216
WRH$_SYSSTAT WRH$_SYSSTA_2248810413_0 TABLE PARTITION 208
10 rows selected.
01:42:31 SYS@cdc> TRUNCATE TABLE WRH$_ACTIVE_SESSION_HISTORY;
Table truncated.
Elapsed: 00:00:00.92
01:42:33 SYS@cdc> Col tablespace_name for a30
01:42:39 SYS@cdc> Col used_pct for a10
01:42:39 SYS@cdc> Set line 120 pages 120
01:42:39 SYS@cdc> select total.tablespace_name,round(total.MB, 2) as Total_MB,round(total.MB - free.MB, 2) as Used_MB,round((1-free.MB / total.MB)* 100, 2) || '%' as Used_Pct
01:42:39 2 from (
01:42:39 3 select tablespace_name, sum(bytes) /1024/1024 as MB
01:42:39 4 from dba_free_space group by tablespace_name) free,
01:42:39 5 (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
01:42:39 6 from dba_data_files group by tablespace_name) total
01:42:39 7 where free.tablespace_name = total.tablespace_name
01:42:39 8 order by 4
01:42:39 9 /
TABLESPACE_NAME TOTAL_MB USED_MB USED_PCT
------------------------------ ---------- ---------- ----------
UNDOTBS1 24630 759.88 3.09%
USERS 78744 30099.5 38.22%
SYSTEM 2048 886.19 43.27%
SYSAUX 17680 7885.13 44.6%
Elapsed: 00:00:00.05
参考链接：
https://blog.itpub.net/26148431/viewspace-2135213/