11g
OL6
Hi ALL,
Happy New Year to ALL!!!
We have issues of ever increasing SYSAUX tablespace caused by AWR data.
Based on the report the object that eats a lot of space in AWR is SM/OPTSTAT and SM/ADVISOR.
How do I purge the two biggest space users above? Or how to drop and recreate them?
Which are their counterpart segments using the query below? or how to map them?
I already tried to drop and recreate the AWR metadata using:
SQL> connect / as sysdba
SQL> @?/rdbms/admin/catnoawr.sql
SQL> @?/rdbms/admin/catawrtb.sql
SQL> select dbid,min(snap_id),max(snap_id) from DBA_HIST_SNAPSHOT group by dbid;
no rows selected
But still the two objects (opstat & advisor) occupying large space in SYSAUX were not recreated and still hugging the space
Do all TABLESPACES have V$OCCUPANTS dictionary view like SYSAUX?
Please help....
Kind regards,