Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Purge Tablespace SYSTEM

Jorge MoralesDec 18 2024

If you have your SYSTEM tablespaces with a high percentage you can do the following to purge OPTSTAT_SNAPSHOT$/I_ OPTSTAT_SNAPSHOT$

SQL> select SEGMENT_NAME, segment_type, bytes/(1024*1024) SIZE_MB from
dba_segments where owner = 'SYS' and segment_name like '%OPTSTAT_SNAPSHOT%';

SEGMENT_NAME SEGMENT_TYPE SIZE_MB
-----------------------------------------------------------------
OPTSTAT_SNAPSHOT$ TABLE 8905
I_OPTSTAT_SNAPSHOT$ INDEX 7827

SQL> execute dbms_stats.purge_stats (DBMS_STATS.PURGE_ALL);

SQL> select SEGMENT_NAME, segment_type, bytes/(1024*1024) SIZE_MB from
dba_segments where owner = 'SYS' and segment_name like '%OPTSTAT_SNAPSHOT%';

SEGMENT_NAME SEGMENT_TYPE SIZE_MB
-----------------------------------------------------------------
OPTSTAT_SNAPSHOT$ TABLE 0.25
I_OPTSTAT_SNAPSHOT$ INDEX 0.0625

Comments
Post Details
Added on Dec 18 2024
1 comment
681 views