Oracle Database 11g: SYSAUX Too large

SYSAUX has a lot of bugs when it comes to purging, and I had to face quite a lot of them. 

My SYSAUX started with this (refer end of blog to find out query used for free space.) 

Tablespace                 Used MB     Free MB    Total MB  Pct. Free
---------------------- ----------- ----------- ----------- ----------

SYSAUX                     230,379      23,535     253,914          9

and ended with this

Tablespace                 Used MB     Free MB    Total MB  Pct. Free
---------------------- ----------- ----------- ----------- ----------

SYSAUX                      17,084     236,830     253,914         93

So what all I had to do 
The number 1 thing is to find out if you are hitting any bugs. How to do that?
Find out table which are of highest size.
You can either run rdbms/admin/awrinfo.sql to get a report or you can run just this

set markup html on spool on 
spool sysaux_segm.html 
set echo on 
with a as (select owner,segment_name,partition_name,segment_type,bytes/1024/1024/1024 from dba_segments where tablespace_name='SYSAUX' order by 5 desc) select * from a where rownum<21; 
spool off 

set markup html off 

This will give you a list of segments which are top consumers of space
in my case they were. 

1. SYS_LOB0000006339C00038$$
2. WRH$_SQL_BIND_METADATA
3. WRH$_SQL_BIND_METADATA_PK
4. SYS_LOB0000006331C00004$$
5. WRH$_ACTIVE_SESSION_HISTORY
6. WRH$_EVENT_HISTOGRAM_PK
7. WRH$_EVENT_HISTOGRAM

and a lot of others. But these are the top ones to start. 
So what do you do. 
For few tables there will be orphaned rows these are 
1. WRH$_EVENT_HISTOGRAM
2. WRH$_ACTIVE_SESSION_HISTORY
3. WRH$_SQL_BIND_METADATA

How to find out
run this ->

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 
);

SELECT COUNT(1) Orphaned_EVENT_Rows 
FROM WRH$_EVENT_HISTOGRAM 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 
); 

For WRH$_SQL_BIND_METADATA, find out the min snap_id from the table and see the min snap_id from wrm$_snapshot



Now how to purge? 
If these rows are too many then use a pl/sql block like this, this will help you commit in batches

Below is okay for EVENT_HISTOGRAM AND ACTIVE_SESSION_HISTORY

declare
v_count integer;
begin

SELECT count(1) into v_count
FROM WRH$_EVENT_HISTOGRAM a <- replace with WRH$_ACTIVE_SESSION_HISTORY
WHERE NOT EXISTS
(SELECT 1
FROM wrm$_snapshot
WHERE snap_id = a.snap_id
AND dbid = a.dbid
AND instance_number = a.instance_number
)and rownum <=100;

while v_count != 0 loop

DELETE
FROM WRH$_EVENT_HISTOGRAM a <- replace with WRH$_ACTIVE_SESSION_HISTORY 
WHERE NOT EXISTS
(SELECT 1
FROM wrm$_snapshot
WHERE snap_id = a.snap_id
AND dbid = a.dbid
AND instance_number = a.instance_number
) and rownum <=100000;
commit;
dbms_output.put_line('Done -');
end loop;
end;

/

For WRH$_SQL_BIND_METADATA use this 

delete from WRH$_SQL_BIND_METADATA tab 

where ( tab.snap_id <= :end_snap_id and 

dbid = :dbid) 

and not exists (select 1 from WRM$_BASELINE b 

where (tab.dbid = b.dbid) and 

(tab.snap_id >= b.start_snap_id) and 

(tab.snap_id <= b.end_snap_id)) 

Queries

AT this stage you should do a manual purge of all the snaps

 

exec dbms_workload_repository.drop_snapshot_range(low_snap_id => 1, high_snap_id=>10000); 

(substitute values accordingly)

the high snap_id is from dba_hist_snapshot or till the snap you want to keep 

After this point you are ready for re-org

you can either do for your tabels

1. shrink space cascade

2. move (Local Index UNUSABLE for Short Period in Alter Table Move Partition (Doc ID 1374199.1)

3. rebuild indexes

4. move lob storage 

Finally depending on the situation you have 

1. How to Reduce SYSAUX Tablespace Occupancy Due to Fragmented TABLEs and INDEXes (Doc ID 1563921.1)

2. How to Resize a Datafile (Doc ID 1029252.6)

Queries
Free Space SYSAUX

col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files where tablespace_name = 'SYSAUX'
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments 
group by tablespace_name) tu

where df.tablespace_name = tu.tablespace_name ;