Oracle10g sysaux space increase and space recovery

Oracle10g sysaux space surges and space recovery. In Oracle10, table space SYSAUX is introduced. oracle stores statistics here to better optimize syst.

Oracle's 10 Gb sysaux space surge and space recovery are introduced in the table space SYSAUX in Oracle10. oracle stores statistics here to better optimize syst.

Oracle10g sysaux space increase and space recovery

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

SELECT occupant_name, space_usage_kbytes from v $ SYSAUX_OCCUPANTS;

Statistics of SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER in oracle are stored in SYSAUX. Here we will focus on SM/OPTSTAT.

SM/OPTSTAT: used to store optimization statistics of earlier versions. In oracle10g, oracle selects an incorrect execution plan when we manually or automatically update statistics. Oracle10g indicates that the statistical information of the old version can be restored. This statistical information is saved for 31 days by default.

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

SQL>

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

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

SQL>

At this time, although the data is deleted, the space has not been recycled. How can we recycle the space?

The space is not released because "purge_stats" deletes data using the delete method. Although the data is gone, HWM has not been downgraded yet. Check which tables OPTSTAT uses and then reduce its high level.
SQL> SELECT s. object_name from dba_objects s where s. object_name like '% OPTSTAT %' and s. object_type = 'table'
2;

OBJECT_NAME
--------------------------------------------------------------------------------
WRI $ _ OPTSTAT_TAB_HISTORY
WRI $ _ OPTSTAT_IND_HISTORY
WRI $ _ OPTSTAT_HISTHEAD_HISTORY
WRI $ _ OPTSTAT_HISTGRM_HISTORY
WRI $ _ OPTSTAT_AUX_HISTORY
WRI $ _ OPTSTAT_OPR
OPTSTAT_HIST_CONTROL $

7 rows selected

SQL>

Use the following SQL statement to determine which table is large and move the table.
SQL> select a. table_name, a. num_rows from dba_tables a where a. tablespace_name = 'sysaux 'and a. table_name like' % OPTSTAT %'
2;

TABLE_NAME NUM_ROWS
----------------------------------------
WRI $ _ optstat_ops 151
WRI $ _ OPTSTAT_AUX_HISTORY 0
WRI $ _ OPTSTAT_HISTGRM_HISTORY 139933
WRI $ _ OPTSTAT_HISTHEAD_HISTORY 14406
WRI $ _ OPTSTAT_IND_HISTORY 1196
WRI $ _ OPTSTAT_TAB_HISTORY 1323

6 rows selected

SQL>

Use the following statement to find the index of the relevant table. Because the index is removed from the table, it will become invalid and you need to re-create the index.
SQL> select I. index_name, I. table_name, I. status, I. table_owner
2 from dba_indexes I, dba_objects s where I. table_name = s. object_name and s. object_name like '% OPTSTAT %' and s. object_type = 'table'
3;

INDEX_NAME TABLE_NAME STATUS TABLE_OWNER
--------------------------------------------------------------------------------------------------
I _WRI $ _ OPTSTAT_TAB_OBJ # _ ST WRI $ _ OPTSTAT_TAB_HISTORY VALID SYS
I _WRI $ _ OPTSTAT_TAB_ST WRI $ _ OPTSTAT_TAB_HISTORY VALID SYS
I _WRI $ _ OPTSTAT_IND_OBJ # _ ST WRI $ _ OPTSTAT_IND_HISTORY VALID SYS
I _WRI $ _ OPTSTAT_IND_ST WRI $ _ OPTSTAT_IND_HISTORY VALID SYS
I _WRI $ _ OPTSTAT_HH_OBJ_ICOL_ST WRI $ _ OPTSTAT_HISTHEAD_HISTORY VALID SYS
I _WRI $ _ OPTSTAT_HH_ST WRI $ _ OPTSTAT_HISTHEAD_HISTORY VALID SYS
I _WRI $ _ OPTSTAT_H_OBJ # _ ICOL # _ ST WRI $ _ OPTSTAT_HISTGRM_HISTORY VALID SYS
I _WRI $ _ OPTSTAT_H_ST WRI $ _ OPTSTAT_HISTGRM_HISTORY VALID SYS
I _WRI $ _ OPTSTAT_AUX_ST WRI $ _ OPTSTAT_AUX_HISTORY VALID SYS
I _WRI $ _ OPTSTAT_OPR_STIME WRI $ _ OPTSTAT_OPR VALID SYS

10 rows selected

SQL>