• Home
  • DBA Scripts
    • Oracle Scripts
    • SQL Server Scripts
  • Knowledge Base
    • Oracle Database
    • MS SQL Server
    • MongoDB
    • MariaDB
  • Troubleshoot
    • Oracle Database Issues
    • SQL Server Issues
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • Courses
    • Oracle Database
    • Oracle DBA L1
    • Oracle DBA L2
    • Oracle DBA L3
  • Home
  • DBA Scripts
    • Oracle Scripts
    • SQL Server Scripts
  • Knowledge Base
    • Oracle Database
    • MS SQL Server
    • MongoDB
    • MariaDB
  • Troubleshoot
    • Oracle Database Issues
    • SQL Server Issues
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • Courses
    • Oracle Database
    • Oracle DBA L1
    • Oracle DBA L2
    • Oracle DBA L3
home/Knowledge Base/Oracle Database/ALL KB Oracle/Purging Oracle Sysaux Tablespace
Popular Search:Oracle, SQL Server, MongoDB

Purging Oracle Sysaux Tablespace

932 views 0 August 27, 2019 September 5, 2019 admin

Restrictions on SYSAUX tablespace

  1. Using SYSAUX DATAFILE clause in the CREATE DATABASE statement you can specify only datafile attributes in SYSAUX tablespace.
  2. You can not alter attributes like (PERMANENT, READ WRITE, EXTENT MANAGEMENT LOCAL, SEGMENT SPACE MANAGEMENT AUTO) with an ALTER TABLESPACE statement.
  3. SYSAUX tablespace cannot be dropped or renamed.

Check whats occupying SYSAUX tablespace:

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.

Run below query to know what all occupants are there in sysaux occupying all the space.

SQL> select occupant_name,occupant_desc,space_usage_kbytes from v$sysaux_occupants;

Once you find what are all occupying the space then we can work on it. If you have tackled with huge number of AWR reports occupying then you need to check AWR retention period.

SQL>select retention from dba_hist_wr_control;

Through this query we can get for how many days AWR reports maintaining, If that which we did not require that many days we can reduce it.

For Example : If we have more than 7 days,we can reduce it to 7 day which is 7*24*60 = 10080 minutes.the retention period is modified to 7 days (10080 minutes) and the interval between each snapshot is 60 minutes through below query.

SQL>execute dbms_workload_repository.modify_snapshot_settings (interval => 60,retention => 10080);

In case while reducing the retention period if we face an  error then check the MOVING_WINDOW_SIZE value with below query

SQL>exec DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size( window_size =>7); SQL>SELECT moving_window_size FROM dba_hist_baseline WHERE baseline_type = ‘MOVING_WINDOW’;

Then update it to correct value and then execute the above AWR retention query.

SQL>execute dbms_workload_repository.modify_snapshot_settings (interval => 60,retention => 10080);

Once the retention period is set you can follow below steps to cleanup the old AWR reports to free up space. Run below query to find the oldest and newest AWR snapshots.

SQL>SELECT snap_id, begin_interval_time, end_interval_timeFROM SYS.WRM$_SNAPSHOT WHERE snap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT) UNION SELECT snap_id, begin_interval_time, end_interval_time FROM SYS.WRM$_SNAPSHOT WHEREsnap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT);

To cleanup all AWR reports between snap_id <start snap> to <end snap> execute below query.

SQL>BEGINdbms_workload_repository.drop_snapshot_range(low_snap_id => <start snap>, high_snap_id=><end snap>);END;/

Else you can run below commands to drop the old AWR reports and rebuild the repositories. This process is very fast too.

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

After clearing up all the AWR reports we were able to get space from SYSAUX tablespace.

High Storage Consumption for LOBs in SYSAUX Tablespace:

A number of LOB segments created for LOB columns of AWR tables in the SYSAUX tablespace consume significant storage.

This storage is not released even after AWR snapshots are dropped and row counts in the tables reduced.

SQL> select * from dba_lobs where table_name = ‘WRH$_SQLTEXT’; SQL> select * from dba_segments where segment_name = ‘<seg-name>’; SQL> select count(*) from WRH$_SQLTEXT;

The LOB segments can be rebuilt using the same name, storage parameters and SYSAUX tablespace by executing a MOVE operation on them.
This builds a new LOB segment containing only the data currently in the LOB and exchanges it with the original LOB segment.

To implement the solution, Execute below steps:

  1. Startup the database in RESTRICT mode to prevent interference from AWR operations during the rebuild.
  2. From a SQL*Plus session connected as SYSDBA perform the move operation as follows:

SQL> alter table wrh$_sqltext move lob(sql_text) store as <name of LOB> tablespace sysaux;

the AWR table is WRH$_SQLTEXT, the LOB column is SQL_TEXT and <name of LOB> is the name of the LOB segment whose storage we want to reclaim

The new LOB segment will have a new data_object_id in dba_objects and a new (header_file,header_block) pair in dba_segments. It should also have a reduced number of bytes and blocks in dba_segments.

  1. Check for and rebuild any unusable index on the table after the LOB move:

SQL> select index_name from dba_indexes where table_name=’WRH$_SQLTEXT’ and status=’UNUSABLE’; INDEX_NAME———–SAMPLE

SQL> alter index SAMPLE rebuild; Index altered.

  1. Finally, restart the database in normal mode.

Other possible solutions

1) ALTER TABLE <> SHRINK SPACE CASCADE on the table (assumes that ASSM and ENABLE ROW MOVEMENT) are used

NOTE: This operation will lock the table during the shrink

2) Use DBMS_REDEFINITION to redefine the table online

NOTE: This operation could require up to double the size of the existing table … if the table does not really have space to release.

 

Tags:Tablespace

Was this helpful?

Yes  No
Related Articles
  • Oracle Dbverify DBV Utility
  • Oracle Database Background Processes
  • Managing the Redo Log
  • Creating a Free Account in Oracle Cloud
  • Understanding Oracle Sysaux Tablespace
  • Understanding Oracle System Tablespace
Leave A Comment Cancel reply

ALL KB Oracle
  • Purging Oracle Sysaux Tablespace
  • Oracle Dbverify DBV Utility
  • Oracle Database Background Processes
  • Managing the Redo Log
  • Creating a Free Account in Oracle Cloud
  • Understanding Oracle Sysaux Tablespace
View All 6  
Popular Articles
  • Upgrade Oracle Database from 11.2.0.3 to 12.1.0.2
  • Upgrading MariaDB on Windows
  • Oracle DBA Basic Interview Questions Part A
  • Purging Oracle Sysaux Tablespace
  • Client – Server Architecture
KB Categories
  • Oracle Database
    • Oracle RAC
    • Oracle ASM
    • Oracle GoldenGate
    • Oracle Tuning
    • Oracle 11g Database
    • Oracle Database Upgrade
    • Oracle 12c Database
    • ALL KB Oracle
    • Oracle 18c Database
    • Oracle Standby Database
  • MongoDB
  • MS SQL Server
  • MySQL
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • MariaDB
Database Organization

Database Organization (DB ORG) is knowledge base for DBA to learn and execute the fundamental of different databases under one website. DB ORG is a non-profit initiative. ORACLE, MS SQL Server, MongoDB, MariaDB, Couchbase

Join Our Community
  • KnowledgeBase
  • Documentation
  • Troubleshoot
  • FAQ
Information Links
  • About DBOrg
  • Licenses
  • Terms
  • Privacy Policy
Contact Us
    DB ORG - Database Administration,
    Knowledge Base for DBA
    Mail: support@databaseorg.com
    WhatsApp: (+91) 9306440957
    Monday to Friday: EST - 11:30 AM to 06:30 PM (IST - 9:00 PM to 4:00 AM)
  • © 2023 Database Organization - DB ORG. All Rights Reserved.

Popular Search:Oracle, SQL Server, MongoDB

WhatsApp DB Org