結合metalink文件454678.1 和在網上看到別人 How to deal with SYSAUX tablespace 100% full ?
The statistics used by Oracle Optimizer inside the Oracle Database Unit (DBU) are updated daily and stored for 31 days by default. For large tables, the statistics can use a significant amount of space in the SYSAUX tablespace

So you have to purge the data manually.but the purge procedure will fail in most of the situation .because during the period , it will use up the UNDOTBS* space.even this, i suggest you should try it firstly. if it fail, then you can run the step 7 direclty.

The following is an example that the earlist data is Sep.4 ,and i want to purge it to the current Oct.11

1. check the oldest datat in it .
su - oracle
sqlplus "/ as sysdba"

SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
04-SEP-08 10.47.40.679300000 PM +05:30

2. purge the old stats data

SQL> exec dbms_stats.purge_stats(to_timestamp('20080915','YYYYMMDD'));
exec dbms_stats.purge_stats(to_timestamp('20080920','YYYYMMDD'));

exec dbms_stats.purge_stats(to_timestamp('20080925','YYYYMMDD'))
exec dbms_stats.purge_stats(to_timestamp('20081001','YYYYMMDD'))

exec dbms_stats.purge_stats(to_timestamp('20081005','YYYYMMDD'))
exec dbms_stats.purge_stats(to_timestamp('20081010','YYYYMMDD'))

3. check the old stats data has been deleted and now the latest are :

SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
11-OCT-08 05.18.08.605495000 PM +05:30

4. Only after the step 1,2,3, now the old data has been pruged. even after this, the SYSAUX table space is still full.
This is a known problem about oracle (Statistics space used by SM/OPTSTAT in the SYSAUX tablespace is not reclaimed after purging).

5. So you can run the awrinfo to check which table or index has occupied the space. then rebuild them. here for example

su - oracle
cd /u01/app/oracle/
sqlplus "/ as sysdba"
sql> @?/rdbms/admin/awrinfo

sql> ! more ./awrinfo.txt
**********************************
(4) Space usage by non-AWR components (> 500K)
**********************************

COMPONENT MB SEGMENT_NAME SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
NON_AWR 528.0 SYS.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX
NON_AWR 470.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY TABLE
NON_AWR 240.0 SYS.I_WRI$_OPTSTAT_H_ST INDEX
NON_AWR 72.0 SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE
NON_AWR 40.0 SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX

6. so we have to move the table and rebuild the index to free the SYSAUX tablespace.

ALTER TABLE SYS.WRI$_OPTSTAT_HISTGRM_HISTORY MOVE;
ALTER TABLE SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY MOVE;

alter index I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild nologging;
alter index I_WRI$_OPTSTAT_H_ST rebuild nologging;
alter index I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST rebuild nologging;

7.So one thing I have to say here is that because the old stats data are only be used to analyze the Oracle/SQL performance to give some refernce to the Oracle Adminstrator , except this, nothing is useful for this. So you can delete the infomation from the stats table direcltly)

Sql> truncate table SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
Sql> truncate table SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY ;

the INDEX is truncated automatically when truncating the table. [@more@]

轉 SYSAUX tablespace 100% full

相關文章