Identify Objects in SYSAUX Tablespace
set linesize 120
set pagesize 100
COLUMN "Item" FORMAT A25
COLUMN "Space Used (GB)" FORMAT 999.99
COLUMN "Schema" FORMAT A25
COLUMN "Move Procedure" FORMAT A40
SELECT occupant_name "Item", space_usage_kbytes/1048576 "Space Used (GB)", schema_name "Schema", move_procedure "Move Procedure"
FROM v$sysaux_occupants
ORDER BY 1;
Item
Space Used (GB) Schema
Move Procedure
------------------------- ---------------
-------------------------
----------------------------------------
AO
.00
SYS
DBMS_AW.MOVE_AWMETA
EM
.08
SYSMAN
emd_maintenance.move_em_tblspc
EM_MONITORING_USER
.00
DBSNMP
EXPRESSION_FILTER
.00
EXFSYS
JOB_SCHEDULER
.00
SYS
LOGMNR
.01
SYSTEM
SYS.DBMS_LOGMNR_D.SET_TABLESPACE
LOGSTDBY
.00
SYSTEM
SYS.DBMS_LOGSTDBY.SET_TABLESPACE
ODM
.00
DMSYS
MOVE_ODM
ORDIM
.00
ORDSYS
ORDIM/PLUGINS
.00
ORDPLUGINS
ORDIM/SQLMM
.00
SI_INFORMTN_SCHEMA
SDO
.00
MDSYS
MDSYS.MOVE_SDO
SM/ADVISOR
.02
SYS
SM/AWR
.15
SYS
SM/OPTSTAT
11.44 SYS
SM/OTHER
.02
SYS
STATSPACK
.00
PERFSTAT
STREAMS
.00
SYS
TEXT
.00
CTXSYS
DRI_MOVE_CTXSYS
TSM
.00
TSMSYS
ULTRASEARCH
.00
WKSYS
MOVE_WK
ULTRASEARCH_DEMO_USER
.00
WK_TEST
MOVE_WK
WM
.00
WMSYS
DBMS_WM.move_proc
XDB
.00
XDB
XDB.DBMS_XDB.MOVEXDB_TABLESPACE
XSAMD
.00
OLAPSYS
DBMS_AMD.Move_OLAP_Catalog
XSOQHIST
.00
SYS
DBMS_XSOQ.OlapiMoveProc
How long old stats are kept
select dbms_stats.get_stats_history_retention from dual;
Set retention of old stats to 10 days
exec dbms_stats.alter_stats_history_retention(10);
--Completely remove old statistics (This is the fastest option. Acts like a truncate command)
EXEC DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL);
Purge stats older than 10 days (best to do this in stages if there is a lot of data (sysdate-30,sydate-25 etc)
exec DBMS_STATS.PURGE_STATS(SYSDATE-10);
Show available stats that have not been purged
select dbms_stats.get_stats_history_availability from dual;
Show how big the tables are and rebuild after stats have been purged
col Mb form 9,999,999
col SEGMENT_NAME form a40
col SEGMENT_TYPE form a6
set lines 120
select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where tablespace_name = 'SYSAUX'
and segment_name like 'WRI$_OPTSTAT%'
and segment_type='TABLE'
group by segment_name,segment_type order by 1 asc;
MB SEGMENT_NAME
SEGMEN
---------- ---------------------------------------- ------
0 WRI$_OPTSTAT_OPR
TABLE
0 WRI$_OPTSTAT_AUX_HISTORY
TABLE
88 WRI$_OPTSTAT_TAB_HISTORY
TABLE
126 WRI$_OPTSTAT_IND_HISTORY
TABLE
158
WRI$_OPTSTAT_HISTGRM_HISTORY
TABLE
4,482
WRI$_OPTSTAT_HISTHEAD_HISTORY
TABLE
Show how big the indexes are ready for a rebuild after stats have been purged
col Mb form 9,999,999
col SEGMENT_NAME form a40
col SEGMENT_TYPE form a6
set lines 120
select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where tablespace_name = 'SYSAUX'
and segment_name like 'WRI$_OPTSTAT%'
and segment_type='INDEX'
group by segment_name,segment_type order by 1 asc;
MB SEGMENT_NAME
SEGMEN
---------- ---------------------------------------- ------
0 WRH$_OPTIMIZER_ENV_PK
INDEX
0 I_WRI$_OPTSTAT_OPR_STIME
INDEX
0 I_WRI$_OPTSTAT_AUX_ST
INDEX
88 I_WRI$_OPTSTAT_TAB_ST
INDEX
105 I_WRI$_OPTSTAT_IND_ST
INDEX
105 I_WRI$_OPTSTAT_H_ST
INDEX
195
I_WRI$_OPTSTAT_TAB_OBJ#_ST
INDEX
213
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST
INDEX
214
I_WRI$_OPTSTAT_IND_OBJ#_ST
INDEX
2,055
I_WRI$_OPTSTAT_HH_ST
INDEX
3,883
I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST
INDEX
Run the rebuild table commands – note that this does cause any gather_stats jobs to fail
alter table
WRI$_OPTSTAT_TAB_HISTORY
move tablespace
sysaux;
alter table WRI$_OPTSTAT_IND_HISTORY
move tablespace sysaux;
alter table WRI$_OPTSTAT_HISTHEAD_HISTORY
move tablespace sysaux;
alter table WRI$_OPTSTAT_HISTGRM_HISTORY
move tablespace sysaux;
alter table WRI$_OPTSTAT_AUX_HISTORY move
tablespace sysaux;
alter table WRI$_OPTSTAT_OPR
move
tablespace sysaux;
alter table WRH$_OPTIMIZER_ENV move
tablespace sysaux;
Alter index
SYS.I_WRI$_OPTSTAT_IND_ST rebuild TABLESPACE SYSAUX;
Alter index SYS.I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild
TABLESPACE
SYSAUX;
Alter index SYS.I_WRI$_OPTSTAT_HH_ST rebuild TABLESPACE
SYSAUX;
Alter index SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST rebuild
TABLESPACE
SYSAUX;
Alter index SYS.I_WRI$_OPTSTAT_TAB_ST rebuild TABLESPACE
SYSAUX;
Alter index SYS.I_WRI$_OPTSTAT_TAB_OBJ#_ST rebuild
TABLESPACE
SYSAUX;
Alter index SYS.I_WRI$_OPTSTAT_OPR_STIME rebuild
TABLESPACE SYSAUX;
Script to generate rebuild statements
select 'alter index '||segment_name||' rebuild online parallel (degree 14);'
from dba_segments
where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='INDEX'
Once completed it is best to check that the indexes (indices) are usable
col INDEX_NAME form a40
select di.index_name,di.index_type,di.status
from dba_indexes di , dba_tables dt
where di.tablespace_name = 'SYSAUX'
and dt.table_name = di.table_name
and di.table_name like '%OPT%'
order by 1 asc;
INDEX_NAME
INDEX_TYPE
STATUS
------------------------------ ---------------------------
--------
I_WRI$_OPTSTAT_AUX_ST
FUNCTION-BASED NORMAL
VALID
I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST
FUNCTION-BASED NORMAL
VALID
I_WRI$_OPTSTAT_HH_ST
FUNCTION-BASED NORMAL
VALID
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST FUNCTION-BASED NORMAL
VALID
I_WRI$_OPTSTAT_H_ST
FUNCTION-BASED NORMAL
VALID
I_WRI$_OPTSTAT_IND_OBJ#_ST
FUNCTION-BASED NORMAL
VALID
I_WRI$_OPTSTAT_IND_ST
FUNCTION-BASED NORMAL
VALID
I_WRI$_OPTSTAT_OPR_STIME
FUNCTION-BASED NORMAL
VALID
I_WRI$_OPTSTAT_TAB_OBJ#_ST
FUNCTION-BASED NORMAL
VALID
I_WRI$_OPTSTAT_TAB_ST
FUNCTION-BASED NORMAL
VALID
WRH$_OPTIMIZER_ENV_PK
NORMAL
VALID
After we clear statistics history, we can optionally rebuild %OPT% tables and indexes in SYSAUX.
--First check status of all %OPT% indexes. They must be VALID before rebuild.
select di.index_name,di.index_type,di.status from dba_indexes di , dba_tables dt
where di.tablespace_name = 'SYSAUX' and dt.table_name = di.table_name
and di.table_name like '%OPT%' order by 1 asc;
--Rebuild tables.
select 'alter table '||segment_name||' move tablespace SYSAUX parallel 8;' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='TABLE';
--Rebuild indexes.
select 'alter index '||segment_name||' rebuild online parallel 8;' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='INDEX';
--Check status of all %OPT% indexes. They must be VALID after rebuild.
select di.index_name,di.index_type,di.status from dba_indexes di , dba_tables dt
where di.tablespace_name = 'SYSAUX' and dt.table_name = di.table_name
and di.table_name like '%OPT%' order by 1 asc;
If above removal process taking too much of time then you can run below two sqls as sysdba to drop the old AWR’s and rebuild the repositories. This process is very fast.
SQL> connect / as sysdba
SQL> @?/rdbms/admin/catnoawr.sql
SQL> @?/rdbms/admin/catawrtb.sql