Monday, October 2, 2017

Cleaning Oracle SYSAUX Tablespace Usage - SYSAUX tablespace is 97.51%

Normally the SYSAUX tablespace is more or less stable so it would be smart to check what is eating the space in there. Connected as a DBA user, run the script ${ORACLE_HOME}/rdbms/admin/utlsyxsz to get the current usage of the SYSAUX tablespace and see how it will grow when you change certain parameters for which you are asked to enter values.

 
OR

select 
   * 
from 
   (select 
      owner,segment_name||'~'||partition_name segment_name,bytes/(1024*1024) meg 
   from 
      dba_segments 
      where tablespace_name = 'SYSAUX' 
   order by 
      blocks desc);

In my case, below 1  occupying most of the space :-

1. SM/AWRSM/AWR — It refers to Automatic Workload Repository.Data in this section is retained for a certain amount of time (default 8 days). Setting can be checked through DBA_HIST_WR_CONTROL.


Retrieve the oldest and latest AWR snapshot

SELECTsnap_id, begin_interval_time, end_interval_timeFROMSYS.WRM$_SNAPSHOTWHEREsnap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT)UNIONSELECTsnap_id, begin_interval_time, end_interval_timeFROMSYS.WRM$_SNAPSHOTWHEREsnap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT)/



Now use the dbms_workload_repository package to remove the AWR snapshots.

BEGINdbms_workload_repository.drop_snapshot_range(low_snap_id => 7521, high_snap_id=>7888);END;/


Speed up ‘removal’ of old AWR reports

@#$%^&*()_ removing the entries takes ages and fails on undo errors … Metalink note Doc ID: 852028.1 states that I can safely remove the AWR metadata tables and recreate them.If none of the above suits as everything is set proper then consider clean up and rebuild AWR repository to clear all the space.

SQL> connect / as sysdba 
SQL> @?/rdbms/admin/catnoawr.sql 
SQL> @?/rdbms/admin/catawrtb.sql

Reference :-WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (Doc ID 387914.1)Suggestions if Your SYSAUX Tablespace Grows Rapidly or Too Large (Doc ID 1292724.1)

2 comments:

Liam said...

Assignmentservicerating is best reviews site.We at Top Quality Assignment believe that there is no shortcut to success and to attain success, hard work, dedication, and commitment must be present. We are an online platform where students check & write reviews for assignments related websites.AllAssignmentHelp reviews 

Liam said...

This is where you might have started your search for assignment help.The previous consumers give their feedback on the website to let the other users know about the quality of their service. For example, if you go to the allassignmenthelp Reviews, you can get to know the opinion of different users about their services. You can also check other websites to compare and select the best service for you.AllAssignmentHelp reviews