Tablespace SYSAUX in a 11g database grows quickly. Run Oracle script awrinfo.sql to find what is using the space. One section of the report shows that ACTIVE_SESSION_HISTORY is the biggest consumer.
SQL> @$ORACLE_HOME/rdbms/admin/awrinfo.sql
COMPONENT MB SEGMENT_NAME - % SPACE_USED SEGMENT_TYPE
--------- ----- ------------------------------ -------------- ---------------
ASH 408.0 WRH$_ACTIVE_SESSION_HISTORY - 98% TABLE PARTITION
.WRH$_ACTIVE_21536658_7139e
ASH 41.0 WRH$_ACTIVE_SESSION_HISTORY_PK - 98% INDEX PARTITION
.WRH$_ACTIVE_21536658_7139
FIXED 36.0 WRH$_SYSMETRIC_HISTORY - 98% TABLE
FIXED 26.0 WRH$_SYSMETRIC_HISTORY_INDEX - 98% INDEX
--
-- Check the Snapshot retention, it works fine (only 8 snapshots exist).
--
SQL> SELECT snap_interval, retention, most_recent_purge_time
FROM sys.wrm$_wr_control;
SNAP_INTERVAL RETENTION MOST_RECENT_PURGE_TIME
----------------- ----------------- -------------------------
+00000 01:00:00.0 +00007 00:00:00.0 27-NOV-13 12.06.06.995 AM
SQL> select dbid, count(*) from SYS.WRM$_SNAPSHOT group by dbid;
DBID COUNT(*)
---------- ----------
21536658 8
-- One interesting thing is DBA_HIST_SNAPSHOT and SYS.WRM$_SNAPSHOT are different
SQL> select * from DBA_HIST_SNAPSHOT;
no rows selected
--
-- Run below query to identify the big tables in SYSAUX
--
SQL> SELECT *
FROM ( SELECT bytes / 1024 / 1024 dim_Mb, segment_name, segment_type
FROM dba_segments
WHERE tablespace_name = 'SYSAUX'
ORDER BY dim_Mb DESC)
WHERE ROWNUM < 5;
DIM_MB SEGMENT_NAME SEGMENT_TYPE
---------- ------------------------------ ------------
544 WRH$_ACTIVE_SESSION_HISTORY TABLE PARTITION
128 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX
88 WRI$_OPTSTAT_HISTGRM_HISTORY TABLE
64 WRH$_SYSMETRIC_HISTORY TABLE
--
-- Follow Oracle DOC ID 387914.1 to clean table WRH$_ACTIVE_SESSION_HISTORY
--
SQL> select table_name,partition_name
2 from dba_tab_partitions
3 where table_name = 'WRH$_ACTIVE_SESSION_HISTORY';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_21536658_7139
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN
SQL> select count(*) from WRH$_ACTIVE_SESSION_HISTORY;
COUNT(*)
----------
1653076
SQL> alter session set "_swrf_test_action" = 72;
Session altered.
SQL>
set serveroutput on
declare
CURSOR cur_part IS
SELECT partition_name from dba_tab_partitions
WHERE table_name = 'WRH$_ACTIVE_SESSION_HISTORY';
query1 varchar2(200);
query2 varchar2(200);
TYPE partrec IS RECORD (snapid number, dbid number);
TYPE partlist IS TABLE OF partrec;
Outlist partlist;
begin
dbms_output.put_line('PARTITION NAME SNAP_ID DBID');
dbms_output.put_line('--------------------------- ------- ----------');
for part in cur_part loop
query1 := 'select min(snap_id), dbid from
sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid';
execute immediate query1 bulk collect into OutList;
if OutList.count > 0 then
for i in OutList.first..OutList.last loop
dbms_output.put_line(part.partition_name||' Min '||OutList(i).snapid||' '||OutList(i).dbid);
end loop;
end if;
query2 := 'select max(snap_id), dbid
from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid';
execute immediate query2 bulk collect into OutList;
if OutList.count > 0 then
for i in OutList.first..OutList.last loop
dbms_output.put_line(part.partition_name||' Max '||OutList(i).snapid||' '||OutList(i).dbid);
dbms_output.put_line('---');
end loop;
end if;
end loop;
end;
/
PARTITION NAME SNAP_ID DBID
----------------------------- ------- ----------
WRH$_ACTIVE_21536658_7139 Min 7139 21536658
WRH$_ACTIVE_21536658_7139 Max 7881 21536658
---
PL/SQL procedure successfully completed.
-- After above PL/SQL block ran, it became three partitions
SQL> select table_name,partition_name
from dba_tab_partitions
where table_name = 'WRH$_ACTIVE_SESSION_HISTORY';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_21536658_7139
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_21536658_7884
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN
-- Run drop Procedure
SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id =>7139,high_snap_id =>7881);
PL/SQL procedure successfully completed.
-- Now, WRH$_ACTIVE_SESSION_HISTORY has no rows
SQL> select count(*) from WRH$_ACTIVE_SESSION_HISTORY;
COUNT(*)
----------
0
SQL> SELECT *
FROM ( SELECT bytes / 1024 / 1024 dim_Mb, segment_name, segment_type
FROM dba_segments
WHERE tablespace_name = 'SYSAUX'
ORDER BY dim_Mb DESC)
WHERE ROWNUM < 5;
DIM_MB SEGMENT_NAME SEGMENT_TYPE
---------- ------------------------------ -----------
128 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX
88 WRI$_OPTSTAT_HISTGRM_HISTORY TABLE
64 WRH$_SYSMETRIC_HISTORY TABLE
55 I_WRI$_OPTSTAT_H_ST INDEX
-- But partitions were NOT dropped
SQL> select table_name,partition_name
from dba_tab_partitions
where table_name = 'WRH$_ACTIVE_SESSION_HISTORY';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_21536658_7139
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_21536658_7884
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN
--
-- Follow DOC ID 287679.1 to shrink the tablespace
--
SQL> SELECT COUNT(1) Orphaned_ASH_Rows
FROM wrh$_active_session_history a
WHERE NOT EXISTS
(SELECT 1
FROM wrm$_snapshot
WHERE snap_id = a.snap_id
AND dbid = a.dbid
AND instance_number = a.instance_number
);
ORPHANED_ASH_ROWS
-----------------
0
-- To reclaim the freed space (since row movement is enabled on WRH$_ACTIVE_SESSION_HISTORY by default)
SQL> alter table WRH$_ACTIVE_SESSION_HISTORY shrink space;
Table altered.
SQL> column OCCUPANT_NAME format a15
SQL> SELECT occupant_name,
occupant_desc,
space_usage_kbytes
FROM v$sysaux_occupants
WHERE occupant_name LIKE '%AWR%';
OCCUPANT_NAME OCCUPANT_DESC SPACE_USAGE_KBYTES
------------- ------------------------------------------------------ ------------------
SM/AWR Server Manageability - Automatic Workload Repository 300416
--
-- Check again after a few days:
-- It seems the old partition was dropped automatically and a new one was created.
SQL> select table_name,partition_name
from dba_tab_partitions
where table_name = 'WRH$_ACTIVE_SESSION_HISTORY';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_21536658_7885
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN
-- And new entries are written into table WRH$_ACTIVE_SESSION_HISTORY
SQL> select count(*) from WRH$_ACTIVE_SESSION_HISTORY;
COUNT(*)
----------
31569
-- while the Snapshot retention keeps unchanged.
SQL> SELECT snap_interval, retention, most_recent_purge_time
FROM sys.wrm$_wr_control;
SNAP_INTERVAL RETENTION MOST_RECENT_PURGE_TIME
----------------- ----------------- -------------------------
+00000 01:00:00.0 +00007 00:00:00.0 11-DEC-13 12.00.29.165 AM
