【Oracle Database】解决system与sysaux表空间过大的问题
SYSTEM--删除数据库审计历史记录
SQL> select *
from (select segment_name, sum(bytes) / 1024 / 1024 mb
from dba_segments
where tablespace_name = 'SYSTEM'
group by segment_name
order by 2 desc)
where rownum < 10;
SEGMENT_NAME MB
--------------------------------------------------------------------------------- ----------
AUD$ 1280
IDL_UB1$ 36
SOURCE$ 23
IDL_UB2$ 16
C_TOID_VERSION# 11
C_OBJ#_INTCOL# 11
SYS_LOB0000001100C00003$$ 9
C_OBJ# 8
ARGUMENT$ 6
9 rows selected.
SQL> select count(*) from AUD$;
COUNT(*)
----------
7056816
SQL> truncate table AUD$;
Table truncated.
SQL> select *
from (select segment_name, sum(bytes) / 1024 / 1024 mb
from dba_segments
where tablespace_name = 'SYSTEM'
group by segment_name
order by 2 desc)
where rownum < 10;
SEGMENT_NAME MB
--------------------------------------------------------------------------------- ----------
IDL_UB1$ 36
SOURCE$ 23
IDL_UB2$ 16
C_TOID_VERSION# 11
C_OBJ#_INTCOL# 11
SYS_LOB0000001100C00003$$ 9
C_OBJ# 8
ARGUMENT$ 6
IDL_CHAR$ 6
9 rows selected.
SYSAUX--删除数据库AWR历史记录
SQL> set line 200
SQL> 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_1478983378_0 TABLE PARTITION 280
WRH$_EVENT_HISTOGRAM_PK WRH$_EVENT__1478983378_0 INDEX PARTITION 112
WRH$_EVENT_HISTOGRAM WRH$_EVENT__1478983378_0 TABLE PARTITION 88
WRI$_ADV_SQLT_PLANS TABLE 72
SYS_LOB0000006409C00004$$ LOBSEGMENT 59
WRH$_SQL_BIND_METADATA TABLE 55
SYS_LOB0000069262C00025$$ LOBSEGMENT 54.1875
WRH$_SQL_BIND_METADATA_PK INDEX 52
WRH$_LATCH WRH$_LATCH_1478983378_0 TABLE PARTITION 51
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX 51
10 rows selected.
SQL> 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>10
order by s.bytes/1024/1024/1024 desc;
'TRUNCATETABLE'||SEGMENT_NAME||';' S.BYTES/1024/1024
--------------------------------------------------------------------------------------------------- -----------------
truncate table WRH$_ACTIVE_SESSION_HISTORY; 280
truncate table WRH$_EVENT_HISTOGRAM; 88
truncate table WRH$_SQL_BIND_METADATA; 55
truncate table WRH$_LATCH; 51
truncate table WRH$_SYSSTAT; 35
truncate table WRH$_SYSMETRIC_HISTORY; 30
truncate table WRH$_LATCH_MISSES_SUMMARY; 28
truncate table WRH$_SQL_PLAN; 27
truncate table WRH$_INST_CACHE_TRANSFER; 27
truncate table WRH$_PARAMETER; 26
truncate table WRH$_SQLSTAT; 24
'TRUNCATETABLE'||SEGMENT_NAME||';' S.BYTES/1024/1024
--------------------------------------------------------------------------------------------------- -----------------
truncate table WRH$_TABLESPACE_STAT; 22
truncate table WRH$_SYSTEM_EVENT; 22
truncate table WRH$_SEG_STAT; 21
truncate table WRH$_DLM_MISC; 11
15 rows selected.