当前位置：首页 > news >正文

oracle 11g SYSAUX表空间清理

news 2024/11/15 20:05:19

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                                  20810 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/

查看全文

http://www.mrgr.cn/news/29103.html