Shrink SYSAUX Tablespace

SQL used to clean up and rebuild objects related to the Oracle Optimizer Statistics Advisor. DBMS_STATS.DROP_ADVISOR_TASK -> This deletes the Statistics Advisor task from the database. ALTER TABLE WRI$_ADV_OBJECTS MOVE; -> This moves the table to a new segment in the tablespace. Effects : Reorganizes the table storage. Removes fragmentation. Frees unused blocks. Rebuilds the table physically. Rebuilding : fixes unusable indexes. removes index fragmentation. improves performance.

oraclesqlmonitoring-alertsv1.0.0

0 stars2 downloads58 views0 comments

By Mahmoud • Created 3/11/2026

Code

(15 lines)

shrink sysaux tablespace
===============================

DECLARE
      v_tname VARCHAR2(32767);
    BEGIN
      v_tname := 'AUTO_STATS_ADVISOR_TASK'; -- Or another task name
      DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
    END;
    /
    
ALTER TABLE WRI$_ADV_OBJECTS MOVE;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 REBUILD;

Shrink SYSAUX Tablespace

Code

General Comments(0)