Print Friendly, PDF & Email
Due to a bug in 12.2 and 18c databases, the SYSAUX tablespace grows rapidly and consumes lots of space in the database. The SM/ADVISOR option is involved.

SYMPTOMS
The SYSAUX tablespace uses too much space on the disk.

-- SYSAUX occupation by segment types

SELECT SEGMENT_TYPE,
       ROUND(SUM(BYTES)/1024/1024) MB
FROM
       DBA_SEGMENTS
WHERE
       TABLESPACE_NAME = 'SYSAUX'
GROUP BY
       SEGMENT_TYPE
ORDER BY 2;
SEGMENT_TYPE               MB
------------------ ----------
NESTED TABLE                1
TABLE SUBPARTITION          2
CLUSTER                     3
LOB PARTITION              12
LOBINDEX                   26
INDEX PARTITION            63
LOBSEGMENT                 96
TABLE PARTITION           197
TABLE                   12426
INDEX                   16801

10 rows selected.

By querying the V$SYSAUX_OCCUPANTS it’s determined that the SM/ADVISOR option ate up all allocated space.

SET LINES 300
SET PAGES 999
COL SCHEMA_NAME FOR A20
COL OCCUPANT_NAME FOR A25 
COL OCCUPANT_DESC FOR A55

SELECT SCHEMA_NAME,
       OCCUPANT_NAME,
       OCCUPANT_DESC,
       ROUND(SPACE_USAGE_KBYTES/1024) SPACE_USAGE_MB
FROM
       V$SYSAUX_OCCUPANTS
ORDER BY SPACE_USAGE_MB;
SCHEMA_NAME          OCCUPANT_NAME             OCCUPANT_DESC                                           SPACE_USAGE_MB
-------------------- ------------------------- ------------------------------------------------------- --------------
TSMSYS               TSM                       Oracle Transparent Session Migration User                            0
EXFSYS               EXPRESSION_FILTER         Expression Filter System                                             0
WK_TEST              ULTRASEARCH_DEMO_USER     Oracle Ultra Search Demo User                                        0
WKSYS                ULTRASEARCH               Oracle Ultra Search                                                  0
SYSMAN               EM                        Enterprise Manager Repository                                        0
SI_INFORMTN_SCHEMA   ORDIM/SI_INFORMTN_SCHEMA  Oracle Multimedia SI_INFORMTN_SCHEMA Components                      0
PERFSTAT             STATSPACK                 Statspack Repository                                                 0
ORDPLUGINS           ORDIM/ORDPLUGINS          Oracle Multimedia ORDPLUGINS Components                              0
OLAPSYS              XSAMD                     OLAP Catalog                                                         0
ORDDATA              ORDIM/ORDDATA             Oracle Multimedia ORDDATA Components                                 0
SYS                  XSOQHIST                  OLAP API History Tables                                              0
ORDSYS               ORDIM                     Oracle Multimedia ORDSYS Components                                  0
MDSYS                SDO                       Oracle Spatial                                                       0
SYS                  AUDIT_TABLES              DB audit tables                                                      0
SYS                  AUTO_TASK                 Automated Maintenance Tasks                                          1
SYS                  AO                        Analytical Workspace Object Table                                    2
SYS                  STREAMS                   Oracle Streams                                                       2
SYSTEM               LOGSTDBY                  Logical Standby                                                      2
DBSNMP               EM_MONITORING_USER        Enterprise Manager Monitoring User                                   2
SYS                  SMON_SCN_TIME             Transaction Layer - SCN to TIME mapping                              3
SYS                  PL/SCOPE                  PL/SQL Identifier Collection                                         3
SYS                  SQL_MANAGEMENT_BASE       SQL Management Base Schema                                           3
CTXSYS               TEXT                      Oracle Text                                                          8
WMSYS                WM                        Workspace Manager                                                    8
SYSTEM               LOGMNR                    LogMiner                                                            11
SYS                  JOB_SCHEDULER             Unified Job Scheduler                                               19
AUDSYS               AUDSYS                    AUDSYS schema objects                                               29
SYS                  SM/OTHER                  Server Manageability - Other Components                             35
XDB                  XDB                       XDB                                                                 63
SYS                  SM/AWR                    Server Manageability - Automatic Workload Repository               133
SYS                  SM/OPTSTAT                Server Manageability - Optimizer Statistics History                357
SYS                  SM/ADVISOR                Server Manageability - Advisor Framework                         28876

32 rows selected.

For the SYSAUX tablespace, the segments like the %WRI$_ADV_OBJECTS% are on the top of tablespace usage

SET LINES 300
SET PAGES 999
COL OWNER FOR A15 
COL SEGMENT_NAME FOR A30 
COL SEGMENT_TYPE FOR A20

WITH TABLES AS (
SELECT OWNER,
       SEGMENT_NAME,
       SEGMENT_TYPE,
       ROUND(SUM(BYTES)/1024/1024) MB
FROM
       DBA_SEGMENTS
WHERE
       TABLESPACE_NAME='SYSAUX' AND
       SEGMENT_TYPE LIKE '%TABLE%'
GROUP BY
       OWNER,
       SEGMENT_NAME,
       SEGMENT_TYPE
HAVING
       -- List all SYSAUX tables larger than 100 MiB
       -- ROUND(SUM(BYTES)/1024/1024) > 100
       ROUND(SUM(BYTES)/1024/1024) > 0
),
INDEXES AS (
SELECT I.OWNER,
       I.INDEX_NAME
FROM
       DBA_INDEXES I,
       TABLES T
WHERE
       I.OWNER=T.OWNER AND
       I.TABLE_NAME=T.SEGMENT_NAME
)
SELECT * FROM TABLES
UNION ALL
-- List all indexes for the found tables 
SELECT D.OWNER,
       D.SEGMENT_NAME,
       D.SEGMENT_TYPE,
       ROUND(SUM(D.BYTES)/1024/1024) MB
FROM
       DBA_SEGMENTS D,
       INDEXES I
WHERE
       D.OWNER=I.OWNER AND
       D.SEGMENT_NAME=I.INDEX_NAME	   
GROUP BY
       D.OWNER,
       D.SEGMENT_NAME,
       D.SEGMENT_TYPE
ORDER BY 4;
 OWNER           SEGMENT_NAME                   SEGMENT_TYPE                 MB
--------------- ------------------------------ -------------------- ----------

...

SYS             WRH$_SYSMETRIC_HISTORY         TABLE PARTITION              26
SYS             I_WRI$_OPTSTAT_H_ST            INDEX                        58
SYS             WRI$_OPTSTAT_HISTGRM_HISTORY   TABLE PARTITION             105
SYS             I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX                       124
SYS             WRI$_ADV_OBJECTS_PK            INDEX                      4536
SYS             WRI$_ADV_OBJECTS_IDX_02        INDEX                      5380
SYS             WRI$_ADV_OBJECTS_IDX_01        INDEX                      6606
SYS             WRI$_ADV_OBJECTS               TABLE                     12343

134 rows selected.

All last four segments of the listing are related to the SM/ADVISOR option.

RELATED
SYSAUX TABLESPACE IS TOO LARGE BECAUSE OF OPTIMIZER STATISTICS HISTORY

DETAILS
According the Doc ID 2360311.1 the OPTIMIZER STATISTICS ADVISOR (SM/ADVISOR) is

Statistics Advisor Framework: Optimizer Statistics Advisor is built-in diagnostic software that analyzes 
the quality of statistics and statistics-related tasks. It uses the same advisor framework as Automatic 
Database Diagnostic Monitor (ADDM), SQL Performance Analyzer, and other advisors. The advisor task runs 
automatically in the maintenance window.

However DBAs can also run it on demand and view the advisor report. If the advisor makes recommendations, 
then in some cases users can run system-generated scripts to implement them.

Optimizer Statistics Advisor inspects how optimizer statistics are gathered. It automatically diagnoses 
problems in the existing practices for gathering statistics. The advisor does not gather a new or 
alternative set of optimizer statistics. The output of the advisor is a report of findings and 
recommendations, which helps DBAs follow best practices for gathering statistics. The Optimizer Statistics 
Advisor framework stores its metadata in data dictionary and dynamic performance views.

Modes of Operation for Statistics Advisor: Optimizer Statistics Advisor supports both an automated and 
manual modes.

Automated Mode: The predefined task AUTO_STATS_ADVISOR_TASK runs automatically in the maintenance window 
once per day. The task runs as part of the automatic optimizer statistics collection client. The automated 
task generates findings and recommendations, but does not implement actions automatically.

As for any other task, you can configure the automated task, and generate reports. To learn how to 
configure the task, please refer to Configuring Automatic Optimizer Statistics Collection. 
REPORT_STATS_ADVISOR_TASK procedure reports the results of an Optimizer Statistics Advisor task.  If the 
report recommends actions, then you can implement the actions manually.

Manual Mode: Users can create their own task using the DBMS_STATS.CREATE_ADVISOR_TASK function, and then 
run it at any time using the EXECUTE_ADVISOR_TASK procedure.

Unlike the automated task, the manual task can implement actions automatically. Alternatively, users can 
configure the task to generate a PL/SQL script, which can be run manually.

The SM/ADVISOR option uses %WRI$_ADV_OBJECTS% segments for storing its data. By default records for Optimizer Statistics Advisor Task are kept for 30 days in the WRI$_ADV_OBJECTS table. And that is true in 19c database. But for instance, in 12.2 and 18c releases due to a bug, records could be kept for an UNLIMITED period of time. And for such releases, it simply means the old records would never be purged and the WRI$_ADV_OBJECTS table and its corresponding indexes will grow in size.

SET PAGES 999
SET LINES 300
COL TASK_NAME FOR A25
COL PARAMETER_NAME FOR A24
COL PARAMETER_VALUE FORMAT A15 

SELECT TASK_NAME,
       PARAMETER_NAME,
       PARAMETER_VALUE
FROM 
       DBA_ADVISOR_PARAMETERS
WHERE
       TASK_NAME='AUTO_STATS_ADVISOR_TASK'  AND 
       PARAMETER_NAME='EXECUTION_DAYS_TO_EXPIRE';
-- 19c

TASK_NAME                 PARAMETER_NAME           PARAMETER_VALUE
------------------------- ------------------------ ---------------
AUTO_STATS_ADVISOR_TASK   EXECUTION_DAYS_TO_EXPIRE 30

SET LINES 300 
COL BANNER_FULL  FOR A75
SELECT BANNER_FULL FROM V$VERSION;

BANNER_FULL
---------------------------------------------------------------------------
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
-- 12c (without applied fix)

TASK_NAME                 PARAMETER_NAME            PARAMETER_VALUE
------------------------- ------------------------- --------------------
AUTO_STATS_ADVISOR_TASK   EXECUTION_DAYS_TO_EXPIRE  UNLIMITED

SET LINES 300 
COL BANNER FOR A75
SELECT BANNER FROM V$VERSION WHERE BANNER LIKE '%Database%';

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

 
In the above listing, one of our 12c databases had this bug, so since AUTO_STATS_ADVISOR_TASK records were not purged from WRI$_ADV_OBJECTS, the SYSAUX space usage was growing rapidly.

SOLUTION
if you are on 12.2 or 18c database releases then apply patches 30138470 (12.2) and 28822489 (18c). It will fix the issue with the EXECUTION_DAYS_TO_EXPIRE set to UNLIMITED. The 19c database release includes fix for this bug. So no need to apply any pach for 19c.

 
When the issue with the EXECUTION_DAYS_TO_EXPIRE is fixed you have 2 options:
1 Wait until data will be purged automatically by the Auto-Purge job that removes all expired records beyond the retention period, or
2 Purge expired records manually.

For instance, to purge all expired records beyond the value of EXECUTION_DAYS_TO_EXPIRE manually

SQL> CONNECT / AS SYSDBA
SQL> EXEC PRVT_ADVISOR.DELETE_EXPIRED_TASKS; 
NOTE

After expired records have been purged, release free space to the SYSAUX tablespace by moving the WRI$_ADV_OBJECTS table and rebuilding its indexes.

SQL> CONNECT / AS SYSDBA
SQL> ALTER TABLE WRI$_ADV_OBJECTS MOVE UPDATE INDEXES;

It is also possible to adjust (reduce or raise) the retention period of the EXECUTION_DAYS_TO_EXPIRE parameter. For instance, by the following statements, the EXECUTION_DAYS_TO_EXPIRE parameter is set to 5 days.

BEGIN
   DBMS_ADVISOR.SET_TASK_PARAMETER(
   task_name=> 'AUTO_STATS_ADVISOR_TASK', 
   parameter=> 'EXECUTION_DAYS_TO_EXPIRE', 
   value => 5);
END;
/

Or

BEGIN 
   DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER
   (task_name => 'AUTO_STATS_ADVISOR_TASK', 
   parameter => 'EXECUTION_DAYS_TO_EXPIRE', 
   value => 5);
END;
/

In addition to the purging of expired records, it is possible to disable the Optimizer Statistics Advisor Task from 12.2 onwards completely. According to the Doc ID 2686022.1 the patch 26749785 adds such functionality as by default there is no way to disable it. With the new fix provided by the patch, a new preference AUTO_STATS_ADVISOR_TASK is added to enable/disable AUTO_STATS_ADVISOR_TASK manually. With the patch installed disable the Optimizer Statistics Advisor Task by the following statements:

-- Get current preference 

SQL> select dbms_stats.get_prefs('AUTO_STATS_ADVISOR_TASK') from dual;

DBMS_STATS.GET_PREFS('AUTO_STATS_ADVISOR_TASK')
--------------------------------------------------------------------------------
TRUE

-- Disable preference 

SQL> exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');

PL/SQL procedure successfully completed.

-- Verify that preference is disabled 

SQL> select dbms_stats.get_prefs('AUTO_STATS_ADVISOR_TASK') from dual;

DBMS_STATS.GET_PREFS('AUTO_STATS_ADVISOR_TASK')
--------------------------------------------------------------------------------
FALSE

TESTCASE

In this testcase I’m about to purge all data but 5 days and then release free space to the SYSAUX tablespace

Database release for this testcase

SET LINES 300 
COL BANNER_FULL  FOR A75
SELECT BANNER_FULL FROM V$VERSION;

BANNER_FULL
---------------------------------------------------------------------------
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

Segments size before purging expired records

SET LINES 300
SET PAGES 999
COL OWNER FOR A5 
COL SEGMENT_NAME FOR A25 
COL SEGMENT_TYPE FOR A10

SELECT OWNER,
       SEGMENT_NAME,
       SEGMENT_TYPE,
       ROUND(BYTES/1024/1024) MB
FROM
       DBA_SEGMENTS
WHERE
       SEGMENT_NAME IN 
('WRI$_ADV_OBJECTS','WRI$_ADV_OBJECTS_IDX_01','WRI$_ADV_OBJECTS_IDX_02','WRI$_ADV_OBJECTS_PK');
OWNER SEGMENT_NAME              SEGMENT_TY         MB
----- ------------------------- ---------- ----------
SYS   WRI$_ADV_OBJECTS          TABLE           12343
SYS   WRI$_ADV_OBJECTS_PK       INDEX            4536
SYS   WRI$_ADV_OBJECTS_IDX_01   INDEX            6606
SYS   WRI$_ADV_OBJECTS_IDX_02   INDEX            5380

Get number of days records are to be kept

SET PAGES 999
SET LINES 300
COL TASK_NAME FOR A25
COL PARAMETER_NAME FOR A24
COL PARAMETER_VALUE FORMAT A15 

SELECT TASK_NAME,
       PARAMETER_NAME,
       PARAMETER_VALUE
FROM 
       DBA_ADVISOR_PARAMETERS
WHERE
       TASK_NAME='AUTO_STATS_ADVISOR_TASK'  AND 
       PARAMETER_NAME='EXECUTION_DAYS_TO_EXPIRE';
TASK_NAME                 PARAMETER_NAME           PARAMETER_VALUE
------------------------- ------------------------ ---------------
AUTO_STATS_ADVISOR_TASK   EXECUTION_DAYS_TO_EXPIRE 30

Get records that are kept

SET PAGES 999
SET LINES 300
COL TASK_NAME FOR A30 
COL EXECUTION_NAME FOR A10 
COL EXECUTION_START FOR A20 

SELECT TASK_ID,
       TASK_NAME,
       EXECUTION_NAME,
       EXECUTION_START
FROM
       DBA_ADVISOR_EXECUTIONS
WHERE
       TASK_NAME='AUTO_STATS_ADVISOR_TASK';

   TASK_ID TASK_NAME                      EXECUTION_ EXECUTION_START
---------- ------------------------------ ---------- --------------------
         4 AUTO_STATS_ADVISOR_TASK        EXEC_4232  18-APR-21
         4 AUTO_STATS_ADVISOR_TASK        EXEC_4244  19-APR-21

...

         4 AUTO_STATS_ADVISOR_TASK        EXEC_4432  16-MAY-21
         4 AUTO_STATS_ADVISOR_TASK        EXEC_4444  17-MAY-21

30 rows selected.

In this testcase I purge all records older than 5 days. So the EXECUTION_DAYS_TO_EXPIRE parameter must be adjusted

-- Set EXECUTION_DAYS_TO_EXPIRE to 5 days

BEGIN
   DBMS_ADVISOR.SET_TASK_PARAMETER(
   task_name=> 'AUTO_STATS_ADVISOR_TASK', 
   parameter=> 'EXECUTION_DAYS_TO_EXPIRE', 
   value => 5);
END;
/

-- Verify parameter is modified 

SELECT TASK_NAME,
       PARAMETER_NAME,
       PARAMETER_VALUE
FROM 
       DBA_ADVISOR_PARAMETERS
WHERE
       TASK_NAME='AUTO_STATS_ADVISOR_TASK'  AND 
       PARAMETER_NAME='EXECUTION_DAYS_TO_EXPIRE';
       
TASK_NAME                 PARAMETER_NAME           PARAMETER_VALUE
------------------------- ------------------------ ---------------
AUTO_STATS_ADVISOR_TASK   EXECUTION_DAYS_TO_EXPIRE 5

Execute PRVT_ADVISOR.DELETE_EXPIRED_TASKS procedure to purge expired records manually

-- User must be SYS

SQL> SHOW USER
USER is "SYS"

-- Get session ID 

SQL> SELECT DISTINCT SID FROM V$MYSTAT;

       SID
----------
       429


-- Run purge statement 

SQL> SET TIMING ON
SQL> EXEC PRVT_ADVISOR.DELETE_EXPIRED_TASKS;

PL/SQL procedure successfully completed.

Elapsed: 00:48:11.64

While purging is in progress, the following query can be used to monitor progress of purging session

SET LINES 300
COL START_TIME FOR A20
COL OPNAME FOR A20
COL ELAPSED FOR 9999999
COL REMAINING FOR 9999999 

SELECT OPNAME,
       SQL_EXEC_ID,
       SOFAR,
       TOTALWORK,
       TO_CHAR(START_TIME,'DD-MON-YYYY HH24:MI:SS') START_TIME,
       ELAPSED_SECONDS ELAPSED,
       TIME_REMAINING REMAINING,
       ROUND(SOFAR/TOTALWORK*100,1) DONE_PCT,
       SQL_ID 
FROM 
       V$SESSION_LONGOPS 
WHERE 
       TOTALWORK != 0 AND 
       SOFAR != TOTALWORK AND 
       SID=&SID;
Enter value for sid: 429
old  15:        SID=&SID
new  15:        SID=429

OPNAME               SQL_EXEC_ID      SOFAR  TOTALWORK START_TIME            ELAPSED REMAINING   DONE_PCT SQL_ID
-------------------- ----------- ---------- ---------- -------------------- -------- --------- ---------- -------------
Index Fast Full Scan    16777232     175993     687476 18-MAY-2021 16:25:19       51       148       25.6 1zrnrntukph5h

OPNAME               SQL_EXEC_ID      SOFAR  TOTALWORK START_TIME            ELAPSED REMAINING   DONE_PCT SQL_ID
-------------------- ----------- ---------- ---------- -------------------- -------- --------- ---------- -------------
Index Fast Full Scan    16777236     248205     687476 18-MAY-2021 16:31:58       28        50       36.1 1zrnrntukph5h

To find sql text by its session sql_id

SET PAGES 999
SET LINES 50
COL SQL_FULLTEXT FOR A50 WORD_WRAPPED 
SET LONG 2000000

SELECT  SQL_FULLTEXT FROM V$SQL WHERE SQL_ID='1zrnrntukph5h' FETCH FIRST ROW ONLY;
SQL_FULLTEXT
--------------------------------------------------
DELETE FROM wri$_adv_objects a
     WHERE a.task_id = :task_id_num AND
         (:execution_name IS NULL OR :execution_
name1 = a.exec_name)
NOTE

As records are purging for number of days the field SQL_EXEC_ID will be changed for every new day. The following query can help to identify which date is purging now

SSET LINES 300
SET PAGES 999
COL SQL_ID FOR A15
COL CHILD_NUMBER FOR 999999
COL NAME FOR A20 
COL POSITION FOR 9999999 
COL VALUE_STRING FOR A10 

SELECT A.SQL_ID,
       A.CHILD_NUMBER,
       A.NAME,
       A.POSITION,
       A.VALUE_STRING
FROM
       V$SQL_BIND_CAPTURE A,
       V$SESSION B
WHERE
       A.SQL_ID=B.SQL_ID AND 
       A.CHILD_NUMBER=B.SQL_CHILD_NUMBER AND 
       B.SID=&SID;
SEnter value for sid: 429
old  12:        B.SID=&SID
new  12:        B.SID=429


SQL_ID          CHILD_NUMBER NAME                 POSITION VALUE_STRING
--------------- ------------ -------------------- -------- ------------
1zrnrntukph5h              1 :TASK_ID_NUM                1 4
1zrnrntukph5h              1 :EXECUTION_NAME             2 EXEC_4352
1zrnrntukph5h              1 :EXECUTION_NAME1            3 EXEC_4352

Look at the value of the VALUE_STRING column. It contains the name of advisor execution name

When purging of expired records is completed find out the size of the segments again

SET LINES 300
SET PAGES 999
COL OWNER FOR A5 
COL SEGMENT_NAME FOR A25 
COL SEGMENT_TYPE FOR A10

SELECT OWNER,
       SEGMENT_NAME,
       SEGMENT_TYPE,
       ROUND(BYTES/1024/1024) MB
FROM
       DBA_SEGMENTS
WHERE
       SEGMENT_NAME IN 
('WRI$_ADV_OBJECTS','WRI$_ADV_OBJECTS_IDX_01','WRI$_ADV_OBJECTS_IDX_02','WRI$_ADV_OBJECTS_PK');
OWNER SEGMENT_NAME              SEGMENT_TY         MB
----- ------------------------- ---------- ----------
SYS   WRI$_ADV_OBJECTS          TABLE           12343
SYS   WRI$_ADV_OBJECTS_PK       INDEX            4536
SYS   WRI$_ADV_OBJECTS_IDX_01   INDEX            6606
SYS   WRI$_ADV_OBJECTS_IDX_02   INDEX            5380

Despite records have been purged successfully, the segments themselves consume the same space on disk. That is why after purging records the MOVE of the WRI$_ADV_OBJECTS table is necessary. The MOVE operation will reorgonized the table.

Verify that records have been purged. The number of available dates must be equal to 5.

SET PAGES 999
SET LINES 300
COL TASK_NAME FOR A30 
COL EXECUTION_NAME FOR A10 
COL EXECUTION_START FOR A20 

SELECT TASK_ID,
       TASK_NAME,
       EXECUTION_NAME,
       EXECUTION_START
FROM
       DBA_ADVISOR_EXECUTIONS
WHERE
       TASK_NAME='AUTO_STATS_ADVISOR_TASK';
   TASK_ID TASK_NAME                      EXECUTION_ EXECUTION_START
---------- ------------------------------ ---------- --------------------
         4 AUTO_STATS_ADVISOR_TASK        EXEC_4414  13-MAY-21
         4 AUTO_STATS_ADVISOR_TASK        EXEC_4424  14-MAY-21
         4 AUTO_STATS_ADVISOR_TASK        EXEC_4430  15-MAY-21
         4 AUTO_STATS_ADVISOR_TASK        EXEC_4432  16-MAY-21
         4 AUTO_STATS_ADVISOR_TASK        EXEC_4444  17-MAY-21


5 rows selected.

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
------------------
18-MAY-21

As was mention earlier after purging records from the WRI$_ADV_OBJECTS table, a freed space must be released to the tablespace. It can be done through the MOVE operation.

-- Indexes status before the MOVE operation

SET PAGES 999
SET LINES 300
COL OWNER FOR A10 
COL INDEX_NAME FOR A30 
COL STATUS FOR A10 
SELECT OWNER, INDEX_NAME, INDEX_TYPE, STATUS FROM DBA_INDEXES WHERE TABLE_NAME='WRI$_ADV_OBJECTS';

OWNER      INDEX_NAME                     INDEX_TYPE                  STATUS
---------- ------------------------------ --------------------------- ----------
SYS        WRI$_ADV_OBJECTS_IDX_01        NORMAL                      VALID
SYS        WRI$_ADV_OBJECTS_IDX_02        FUNCTION-BASED NORMAL       VALID
SYS        SYS_IL0000008705C00008$$       LOB                         VALID
SYS        SYS_IL0000008705C00025$$       LOB                         VALID
SYS        WRI$_ADV_OBJECTS_PK            NORMAL                      VALID

-- Move table to the same tablespace

SQL> ALTER TABLE WRI$_ADV_OBJECTS MOVE;

Table altered.

When the MOVE operation is completed three related table indexes become UNUSABLE so they have to be rebuild

-- Indexes status after the MOVE operation

SELECT OWNER, INDEX_NAME, INDEX_TYPE, STATUS FROM DBA_INDEXES WHERE TABLE_NAME='WRI$_ADV_OBJECTS';

OWNER      INDEX_NAME                     INDEX_TYPE                  STATUS
---------- ------------------------------ --------------------------- ----------
SYS        WRI$_ADV_OBJECTS_IDX_01        NORMAL                      UNUSABLE
SYS        WRI$_ADV_OBJECTS_IDX_02        FUNCTION-BASED NORMAL       UNUSABLE
SYS        SYS_IL0000008705C00008$$       LOB                         VALID
SYS        SYS_IL0000008705C00025$$       LOB                         VALID
SYS        WRI$_ADV_OBJECTS_PK            NORMAL                      UNUSABLE
 -- Rebuild indexes

ALTER INDEX SYS.WRI$_ADV_OBJECTS_IDX_01 REBUILD;
ALTER INDEX SYS.WRI$_ADV_OBJECTS_IDX_02 REBUILD;
ALTER INDEX SYS.WRI$_ADV_OBJECTS_PK REBUILD;

Verify that indexes become VALID again

SQL> SELECT OWNER, INDEX_NAME, STATUS FROM DBA_INDEXES WHERE TABLE_NAME='WRI$_ADV_OBJECTS';

OWNER      INDEX_NAME                     STATUS
---------- ------------------------------ ----------
SYS        WRI$_ADV_OBJECTS_IDX_01        VALID
SYS        WRI$_ADV_OBJECTS_IDX_02        VALID
SYS        SYS_IL0000008705C00008$$       VALID
SYS        SYS_IL0000008705C00025$$       VALID
SYS        WRI$_ADV_OBJECTS_PK            VALID

Get size of segments right after segments have been reorganized

SET LINES 300
SET PAGES 999
COL OWNER FOR A5 
COL SEGMENT_NAME FOR A25 
COL SEGMENT_TYPE FOR A10

SELECT OWNER,
       SEGMENT_NAME,
       SEGMENT_TYPE,
       ROUND(BYTES/1024/1024) MB
FROM
       DBA_SEGMENTS
WHERE
       SEGMENT_NAME IN 
('WRI$_ADV_OBJECTS','WRI$_ADV_OBJECTS_IDX_01','WRI$_ADV_OBJECTS_IDX_02','WRI$_ADV_OBJECTS_PK');
OWNER SEGMENT_NAME              SEGMENT_TY         MB
----- ------------------------- ---------- ----------
SYS   WRI$_ADV_OBJECTS          TABLE            2048
SYS   WRI$_ADV_OBJECTS_PK       INDEX             416
SYS   WRI$_ADV_OBJECTS_IDX_01   INDEX             616
SYS   WRI$_ADV_OBJECTS_IDX_02   INDEX             520

So after MOVE operation segments reduced in size.

Also, the table can be moved with the UPDATE INDEXES keyword. Using this keyword eliminates manual rebuilding of the table indexes

SQL> ALTER TABLE WRI$_ADV_OBJECTS MOVE UPDATE INDEXES;

Table altered.

SQL> SELECT OWNER, INDEX_NAME, STATUS FROM DBA_INDEXES WHERE TABLE_NAME='WRI$_ADV_OBJECTS';

OWNER      INDEX_NAME                     STATUS
---------- ------------------------------ ----------
SYS        WRI$_ADV_OBJECTS_IDX_01        VALID
SYS        WRI$_ADV_OBJECTS_IDX_02        VALID
SYS        SYS_IL0000008705C00008$$       VALID
SYS        SYS_IL0000008705C00025$$       VALID
SYS        WRI$_ADV_OBJECTS_PK            VALID

REFERENCES
How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)
How To Disable Optimizer Statistics Advisor From 12.2 Onwards(Doc ID 2686022.1)
SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 or Above Due To Statistics Advisor(Doc ID 2305512.1)
Optimizer Statistics Advisor In 12.2 (Quick Overview) (Doc ID 2259398.1)
12c Release 2 New Feature : Statistics Advisor (Walk-Through) (Doc ID 2360311.1)
Alter Table Move Online Discard Commit Transaction For SYS Objects (Doc ID 2338206.1)

 
 

Version  : 17:51 18.05.2021
Database : 19.5.0.0.0