1. Check the size of the sysaux tablespace items.
SQL> 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
/SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7
Item Space Used (GB) Schema Move Procedure
------------------------- --------------- ------------------------- ----------------------------------------
AO .04 SYS DBMS_AW.MOVE_AWMETA
AUDIT_TABLES .00 SYS DBMS_AUDIT_MGMT.move_dbaudit_tables
AUDSYS .00 AUDSYS
AUTO_TASK .00 SYS
EM .00 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
ORDIM .00 ORDSYS ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDDATA .02 ORDDATA ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDPLUGINS .00 ORDPLUGINS ordsys.ord_admin.move_ordim_tblspc
ORDIM/SI_INFORMTN_SCHEMA .00 SI_INFORMTN_SCHEMA ordsys.ord_admin.move_ordim_tblspc
PL/SCOPE .00 SYS
SDO .08 MDSYS MDSYS.MOVE_SDO
SM/ADVISOR .01 SYS
SM/AWR .04 SYS
SM/OPTSTAT .01 SYS
SM/OTHER .10 SYS
SMON_SCN_TIME .00 SYS
SQL_MANAGEMENT_BASE .00 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 .01 WMSYS DBMS_WM.move_proc
XDB .07 XDB XDB.DBMS_XDB_ADMIN.MOVEXDB_TABLESPACE
XSAMD .00 OLAPSYS DBMS_AMD.Move_OLAP_Catalog
XSOQHIST .04 SYS DBMS_XSOQ.OlapiMoveProc
32 rows selected.
2. To check the retention time.
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
3. To change the retention for stats history.
SQL> exec dbms_stats.alter_stats_history_retention(10);
PL/SQL procedure successfully completed.
4. Check the retions changed.
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
10
5. Purge the stats older than 10days.
SQL> exec DBMS_STATS.PURGE_STATS(SYSDATE-10);
PL/SQL procedure successfully completed.
6. check the optstats tables size after purging.
SQL> 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 ascSQL> SQL> SQL> SQL> 2 3 4 5
6 /
MB SEGMENT_NAME SEGMEN
---------- ---------------------------------------- ------
0 WRI$_OPTSTAT_SYNOPSIS_PARTGRP TABLE
0 WRI$_OPTSTAT_SYNOPSIS_HEAD$ TABLE
0 WRI$_OPTSTAT_AUX_HISTORY TABLE
0 WRI$_OPTSTAT_IND_HISTORY TABLE
0 WRI$_OPTSTAT_TAB_HISTORY TABLE
0 WRI$_OPTSTAT_OPR TABLE
1 WRI$_OPTSTAT_OPR_TASKS TABLE
7 rows selected.
7. check the index size belogs to OPT tables.
SQL> 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 '%OPT%'
and segment_type='INDEX'
group by segment_name,segment_type order by 1 asc
/SQL> SQL> SQL> SQL> 2 3 4 5 6
MB SEGMENT_NAME SEGMEN
---------- ---------------------------------------- ------
0 I_OPT_SQLSTAT_LGT INDEX
0 I_OPT_DIRECTIVE_DIROWN# INDEX
0 I_OPT_DIRECTIVE_OWN# INDEX
0 I_WRI$_OPTSTAT_OPR_ID INDEX
0 I_WRI$_OPTSTAT_SYNOPHEAD INDEX
0 I_OPT_FINDING_F_ID INDEX
0 I_OPT_DIRECTIVE_DIRID INDEX
0 I_OPT_FINDING_OBJ_ID_OBJ_TYPE INDEX
0 WRH$_PLAN_OPTION_NAME_PK INDEX
0 I_WRI$_OPTSTAT_OPR_STIME INDEX
0 I_WRI$_OPTSTAT_SYNOPPARTGRP INDEX
0 WRH$_OPTIMIZER_ENV_PK INDEX
0 PK_OPT_SQLSTAT INDEX
0 I_WRI$_OPTSTAT_AUX_ST INDEX
0 I_WRI$_OPTSTAT_IND_ST INDEX
0 I_WRI$_OPTSTAT_TAB_ST INDEX
0 I_WRI$_OPTSTAT_IND_OBJ#_ST INDEX
0 I_WRI$_OPTSTAT_TAB_OBJ#_ST INDEX
0 I_WRI$_OPTSTAT_OPR_TASKS_OPID INDEX
0 I_WRI$_OPTSTAT_OPR_TASKS_STIME INDEX
0 I_WRI$_OPTSTAT_OPR_TASKS_OPOBJ INDEX
1 I_WRI$_OPTSTAT_H_ST INDEX
1 I_WRI$_OPTSTAT_OPR_TASKS_TGST INDEX
1 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX
1 I_WRI$_OPTSTAT_HH_ST INDEX
2 I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX
26 rows selected.
9. To reclaim the space in sysaux tablespace, we need reorg OPT tables.
SQL> select 'alter table '||segment_name||' move tablespace SYSAUX;' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='TABLE' 2 ;
'ALTERTABLE'||SEGMENT_NAME||'MOVETABLESPACESYSAUX;'
------------------------------------------------------------------------------------------------------------------------
alter table WRI$_OPTSTAT_TAB_HISTORY move tablespace SYSAUX;
alter table WRI$_OPTSTAT_IND_HISTORY move tablespace SYSAUX;
alter table WRI$_OPTSTAT_AUX_HISTORY move tablespace SYSAUX;
alter table WRI$_OPTSTAT_OPR move tablespace SYSAUX;
alter table WRI$_OPTSTAT_OPR_TASKS move tablespace SYSAUX;
alter table OPT_FINDING$ move tablespace SYSAUX;
alter table OPT_FINDING_OBJ$ move tablespace SYSAUX;
alter table OPT_DIRECTIVE_OWN$ move tablespace SYSAUX;
alter table OPT_DIRECTIVE$ move tablespace SYSAUX;
alter table WRH$_OPTIMIZER_ENV move tablespace SYSAUX;
alter table WRH$_PLAN_OPTION_NAME move tablespace SYSAUX;
alter table WRI$_OPTSTAT_SYNOPSIS_PARTGRP move tablespace SYSAUX;
alter table WRI$_OPTSTAT_SYNOPSIS_HEAD$ move tablespace SYSAUX;
13 rows selected.
10. To reclaim the space in sysaux tablespace, we need reorg OPT Index.
SQL> 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' 2
3 ;
'ALTERINDEX'||SEGMENT_NAME||'REBUILDONLINEPARALLEL(DEGREE14);'
------------------------------------------------------------------------------------------------------------------------
alter index I_WRI$_OPTSTAT_TAB_OBJ#_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_TAB_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_IND_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_AUX_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_OPR_STIME rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_OPR_ID rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_OPR_TASKS_TGST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_OPR_TASKS_OPID rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_OPR_TASKS_STIME rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_OPR_TASKS_OPOBJ rebuild online parallel (degree 14);
alter index I_OPT_FINDING_F_ID rebuild online parallel (degree 14);
alter index I_OPT_FINDING_OBJ_ID_OBJ_TYPE rebuild online parallel (degree 14);
alter index I_OPT_DIRECTIVE_OWN# rebuild online parallel (degree 14);
alter index I_OPT_DIRECTIVE_DIRID rebuild online parallel (degree 14);
alter index I_OPT_DIRECTIVE_DIROWN# rebuild online parallel (degree 14);
alter index PK_OPT_SQLSTAT rebuild online parallel (degree 14);
alter index I_OPT_SQLSTAT_LGT rebuild online parallel (degree 14);
alter index WRH$_OPTIMIZER_ENV_PK rebuild online parallel (degree 14);
alter index WRH$_PLAN_OPTION_NAME_PK rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_SYNOPPARTGRP rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_SYNOPHEAD rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_HH_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_H_ST rebuild online parallel (degree 14);
26 rows selected.
11. Finally check the items size in the sysaux occupents.
SQL> 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
/SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7
Item Space Used (GB) Schema Move Procedure
------------------------- --------------- ------------------------- ----------------------------------------
AO .04 SYS DBMS_AW.MOVE_AWMETA
AUDIT_TABLES .00 SYS DBMS_AUDIT_MGMT.move_dbaudit_tables
AUDSYS .00 AUDSYS
AUTO_TASK .00 SYS
EM .00 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
ORDIM .00 ORDSYS ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDDATA .02 ORDDATA ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDPLUGINS .00 ORDPLUGINS ordsys.ord_admin.move_ordim_tblspc
ORDIM/SI_INFORMTN_SCHEMA .00 SI_INFORMTN_SCHEMA ordsys.ord_admin.move_ordim_tblspc
PL/SCOPE .00 SYS
SDO .08 MDSYS MDSYS.MOVE_SDO
SM/ADVISOR .01 SYS
SM/AWR .04 SYS
SM/OPTSTAT .01 SYS
SM/OTHER .10 SYS
SMON_SCN_TIME .00 SYS
SQL_MANAGEMENT_BASE .00 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 .01 WMSYS DBMS_WM.move_proc
XDB .07 XDB XDB.DBMS_XDB_ADMIN.MOVEXDB_TABLESPACE
XSAMD .00 OLAPSYS DBMS_AMD.Move_OLAP_Catalog
XSOQHIST .04 SYS DBMS_XSOQ.OlapiMoveProc
32 rows selected.
SQL> 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
/SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7
Item Space Used (GB) Schema Move Procedure
------------------------- --------------- ------------------------- ----------------------------------------
AO .04 SYS DBMS_AW.MOVE_AWMETA
AUDIT_TABLES .00 SYS DBMS_AUDIT_MGMT.move_dbaudit_tables
AUDSYS .00 AUDSYS
AUTO_TASK .00 SYS
EM .00 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
ORDIM .00 ORDSYS ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDDATA .02 ORDDATA ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDPLUGINS .00 ORDPLUGINS ordsys.ord_admin.move_ordim_tblspc
ORDIM/SI_INFORMTN_SCHEMA .00 SI_INFORMTN_SCHEMA ordsys.ord_admin.move_ordim_tblspc
PL/SCOPE .00 SYS
SDO .08 MDSYS MDSYS.MOVE_SDO
SM/ADVISOR .01 SYS
SM/AWR .04 SYS
SM/OPTSTAT .01 SYS
SM/OTHER .10 SYS
SMON_SCN_TIME .00 SYS
SQL_MANAGEMENT_BASE .00 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 .01 WMSYS DBMS_WM.move_proc
XDB .07 XDB XDB.DBMS_XDB_ADMIN.MOVEXDB_TABLESPACE
XSAMD .00 OLAPSYS DBMS_AMD.Move_OLAP_Catalog
XSOQHIST .04 SYS DBMS_XSOQ.OlapiMoveProc
32 rows selected.
2. To check the retention time.
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
3. To change the retention for stats history.
SQL> exec dbms_stats.alter_stats_history_retention(10);
PL/SQL procedure successfully completed.
4. Check the retions changed.
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
10
5. Purge the stats older than 10days.
SQL> exec DBMS_STATS.PURGE_STATS(SYSDATE-10);
PL/SQL procedure successfully completed.
6. check the optstats tables size after purging.
SQL> 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 ascSQL> SQL> SQL> SQL> 2 3 4 5
6 /
MB SEGMENT_NAME SEGMEN
---------- ---------------------------------------- ------
0 WRI$_OPTSTAT_SYNOPSIS_PARTGRP TABLE
0 WRI$_OPTSTAT_SYNOPSIS_HEAD$ TABLE
0 WRI$_OPTSTAT_AUX_HISTORY TABLE
0 WRI$_OPTSTAT_IND_HISTORY TABLE
0 WRI$_OPTSTAT_TAB_HISTORY TABLE
0 WRI$_OPTSTAT_OPR TABLE
1 WRI$_OPTSTAT_OPR_TASKS TABLE
7 rows selected.
7. check the index size belogs to OPT tables.
SQL> 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 '%OPT%'
and segment_type='INDEX'
group by segment_name,segment_type order by 1 asc
/SQL> SQL> SQL> SQL> 2 3 4 5 6
MB SEGMENT_NAME SEGMEN
---------- ---------------------------------------- ------
0 I_OPT_SQLSTAT_LGT INDEX
0 I_OPT_DIRECTIVE_DIROWN# INDEX
0 I_OPT_DIRECTIVE_OWN# INDEX
0 I_WRI$_OPTSTAT_OPR_ID INDEX
0 I_WRI$_OPTSTAT_SYNOPHEAD INDEX
0 I_OPT_FINDING_F_ID INDEX
0 I_OPT_DIRECTIVE_DIRID INDEX
0 I_OPT_FINDING_OBJ_ID_OBJ_TYPE INDEX
0 WRH$_PLAN_OPTION_NAME_PK INDEX
0 I_WRI$_OPTSTAT_OPR_STIME INDEX
0 I_WRI$_OPTSTAT_SYNOPPARTGRP INDEX
0 WRH$_OPTIMIZER_ENV_PK INDEX
0 PK_OPT_SQLSTAT INDEX
0 I_WRI$_OPTSTAT_AUX_ST INDEX
0 I_WRI$_OPTSTAT_IND_ST INDEX
0 I_WRI$_OPTSTAT_TAB_ST INDEX
0 I_WRI$_OPTSTAT_IND_OBJ#_ST INDEX
0 I_WRI$_OPTSTAT_TAB_OBJ#_ST INDEX
0 I_WRI$_OPTSTAT_OPR_TASKS_OPID INDEX
0 I_WRI$_OPTSTAT_OPR_TASKS_STIME INDEX
0 I_WRI$_OPTSTAT_OPR_TASKS_OPOBJ INDEX
1 I_WRI$_OPTSTAT_H_ST INDEX
1 I_WRI$_OPTSTAT_OPR_TASKS_TGST INDEX
1 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX
1 I_WRI$_OPTSTAT_HH_ST INDEX
2 I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX
26 rows selected.
9. To reclaim the space in sysaux tablespace, we need reorg OPT tables.
SQL> select 'alter table '||segment_name||' move tablespace SYSAUX;' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='TABLE' 2 ;
'ALTERTABLE'||SEGMENT_NAME||'MOVETABLESPACESYSAUX;'
------------------------------------------------------------------------------------------------------------------------
alter table WRI$_OPTSTAT_TAB_HISTORY move tablespace SYSAUX;
alter table WRI$_OPTSTAT_IND_HISTORY move tablespace SYSAUX;
alter table WRI$_OPTSTAT_AUX_HISTORY move tablespace SYSAUX;
alter table WRI$_OPTSTAT_OPR move tablespace SYSAUX;
alter table WRI$_OPTSTAT_OPR_TASKS move tablespace SYSAUX;
alter table OPT_FINDING$ move tablespace SYSAUX;
alter table OPT_FINDING_OBJ$ move tablespace SYSAUX;
alter table OPT_DIRECTIVE_OWN$ move tablespace SYSAUX;
alter table OPT_DIRECTIVE$ move tablespace SYSAUX;
alter table WRH$_OPTIMIZER_ENV move tablespace SYSAUX;
alter table WRH$_PLAN_OPTION_NAME move tablespace SYSAUX;
alter table WRI$_OPTSTAT_SYNOPSIS_PARTGRP move tablespace SYSAUX;
alter table WRI$_OPTSTAT_SYNOPSIS_HEAD$ move tablespace SYSAUX;
13 rows selected.
10. To reclaim the space in sysaux tablespace, we need reorg OPT Index.
SQL> 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' 2
3 ;
'ALTERINDEX'||SEGMENT_NAME||'REBUILDONLINEPARALLEL(DEGREE14);'
------------------------------------------------------------------------------------------------------------------------
alter index I_WRI$_OPTSTAT_TAB_OBJ#_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_TAB_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_IND_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_AUX_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_OPR_STIME rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_OPR_ID rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_OPR_TASKS_TGST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_OPR_TASKS_OPID rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_OPR_TASKS_STIME rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_OPR_TASKS_OPOBJ rebuild online parallel (degree 14);
alter index I_OPT_FINDING_F_ID rebuild online parallel (degree 14);
alter index I_OPT_FINDING_OBJ_ID_OBJ_TYPE rebuild online parallel (degree 14);
alter index I_OPT_DIRECTIVE_OWN# rebuild online parallel (degree 14);
alter index I_OPT_DIRECTIVE_DIRID rebuild online parallel (degree 14);
alter index I_OPT_DIRECTIVE_DIROWN# rebuild online parallel (degree 14);
alter index PK_OPT_SQLSTAT rebuild online parallel (degree 14);
alter index I_OPT_SQLSTAT_LGT rebuild online parallel (degree 14);
alter index WRH$_OPTIMIZER_ENV_PK rebuild online parallel (degree 14);
alter index WRH$_PLAN_OPTION_NAME_PK rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_SYNOPPARTGRP rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_SYNOPHEAD rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_HH_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild online parallel (degree 14);
alter index I_WRI$_OPTSTAT_H_ST rebuild online parallel (degree 14);
26 rows selected.
11. Finally check the items size in the sysaux occupents.
SQL> 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
/SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7
Item Space Used (GB) Schema Move Procedure
------------------------- --------------- ------------------------- ----------------------------------------
AO .04 SYS DBMS_AW.MOVE_AWMETA
AUDIT_TABLES .00 SYS DBMS_AUDIT_MGMT.move_dbaudit_tables
AUDSYS .00 AUDSYS
AUTO_TASK .00 SYS
EM .00 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
ORDIM .00 ORDSYS ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDDATA .02 ORDDATA ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDPLUGINS .00 ORDPLUGINS ordsys.ord_admin.move_ordim_tblspc
ORDIM/SI_INFORMTN_SCHEMA .00 SI_INFORMTN_SCHEMA ordsys.ord_admin.move_ordim_tblspc
PL/SCOPE .00 SYS
SDO .08 MDSYS MDSYS.MOVE_SDO
SM/ADVISOR .01 SYS
SM/AWR .04 SYS
SM/OPTSTAT .01 SYS
SM/OTHER .10 SYS
SMON_SCN_TIME .00 SYS
SQL_MANAGEMENT_BASE .00 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 .01 WMSYS DBMS_WM.move_proc
XDB .07 XDB XDB.DBMS_XDB_ADMIN.MOVEXDB_TABLESPACE
XSAMD .00 OLAPSYS DBMS_AMD.Move_OLAP_Catalog
XSOQHIST .04 SYS DBMS_XSOQ.OlapiMoveProc
32 rows selected.
No comments:
Post a Comment