Sysaux tablespace Insufficiency

Oracle introduced from 10 Gb. Some database components that previously used standalone or system tablespace are now created in the SYSAUX tablespace. By separating these components and functions, S

Oracle introduced from 10 Gb. Some database components that previously used standalone or system tablespace are now created in the SYSAUX tablespace. By separating these components and functions, S

An error occurred in the alter log of the backup database on site.
ORA-1688: unable to extend table SYS. WRH $ _ SQLSTAT partition WRH $ _ SQLSTA_3344221469_3956 by 128 in tablespace SYSAUX
ORA-1688: unable to extend table SYS. WRH $ _ SQLSTAT partition WRH $ _ SQLSTA_3344221469_3956 by 128 in tablespace SYSAUX

According to the error message, the sysaux tablespace is full.

Oracle introduced from 10 Gb. Some database components that used independent tablespace or system tablespace are now created in SYSAUX tablespace.
By separating these components and functions, the load on the SYSTEM tablespace is reduced. Repeated creation of related objects and components can avoid the fragmentation problem of the SYSTEM tablespace.
If the SYSAUX tablespace is unavailable, the core functions of the database will remain effective. If you use the SYSAUX tablespace, the features will fail or the features will be limited.

You can view the situation from V $ SYSAUX_OCCUPANTS view.

V $ SYSAUX_OCCUPANTS

V $ SYSAUX_OCCUPANTS displays SYSAUX tablespace occupant information.
Column Datatype Description
OCCUPANT_NAME VARCHAR2 (64) Occupant name
OCCUPANT_DESC VARCHAR2 (64) Occupant description
SCHEMA_NAME VARCHAR2 (64) Schema name for the occupant
MOVE_PROCEDURE VARCHAR2 (64) Name of the move procedure; null if not applicable
MOVE_PROCEDURE_DESC VARCHAR2 (64) Description of the move procedure
SPACE_USAGE_KBYTES NUMBER Current space usage of the occupant (in KB)

View the occupant space usage

Select OCCUPANT_NAME, SPACE_USAGE_KBYTES/1024/1024 from V $ SYSAUX_OCCUPANTS;
SELECT s. object_name from dba_objects s where s. object_name like '% OPTSTAT %' and s. object_type = 'table ';

In Oracle10, table space SYSAUX is introduced. oracle stores statistics here to better optimize the system table space,
We can use view V $ SYSAUX_OCCUPANTS to check which data is stored in SYSAUX.

Statistics of SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER in oracle are stored in SYSAUX.

Query the storage time of the Current SM/OPTSTAT statistics
SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
31

Save the statistical information of SM/OPTSTAT for 10 days.
SQL> exec dbms_stats.alter_stats_history_retention (10 );

PL/SQL procedure successfully completed

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
10

Delete the statistics generated 16 days ago
SQL> exec dbms_stats.purge_stats (sysdate-20 );

PL/SQL procedure successfully completed

SQL>

View the time when the current valid statistics are generated.
SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;

GET_STATS_HISTORY_AVAILABILITY
--------------------------------------------------------------------------------
12-2-12 07.15.49.000000000 PM + 08:00

Delete the Statistical Data seven days ago.
SQL> exec dbms_stats.purge_stats (sysdate-7 );

PL/SQL procedure successfully completed

At this time, we found that the effective statistical information time has changed.
SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;

GET_STATS_HISTORY_AVAILABILITY
--------------------------------------------------------------------------------
14-2-12 07.15.57.000000000 PM + 08:00

,