12 Replies Latest reply: Jul 9, 2013 4:57 AM by Karan RSS

    Purging SYSAUX table space

    user12009184

      hi,

       

      i have one production database in that sysaux tablespace size was 80Gb due to statistics_level=ALL now we had change to typical after that 75Gb space get free but we can not resize this datafiles because database was scatterd what we need to do to shrink this tablespace as well as datafile;

       

      now only 3GB of size used in 80GB tablespace.

       

      what action we need to take to shrink the tablespace as well as files

       

       

      Thanks .

        • 1. Re: Purging SYSAUX table space
          Hemant K Chitale

          >now only 3GB of size used in 80GB tablespace.

           

          How did you compute that ?  Did you actually rebuild tables and indexes ?

           

           

          See Oracle Support article "How to Reduce SYSAUX Tablespace Occupancy Due to Fragmented TABLEs and INDEXes [ID 1271178.1]"

           

           

          Hemant K Chitale


          • 2. Re: Purging SYSAUX table space
            user12009184

            hi,

             

            thanks for reply..

             

            but the below is the occupent.. in sysaux tablespace..

            STATSPACK    PERFSTAT    979.75

            SM/AWR    SYS    424.5

            SM/OPTSTAT    SYS    418.25

            SM/ADVISOR    SYS    107.3125

            WM    WMSYS    7.125

            SM/OTHER    SYS    6.6875

            LOGMNR    SYSTEM    5.9375

            EM_MONITORING_USER    DBSNMP    1.5625

            LOGSTDBY    SYSTEM    0.875

            AO    SYS    0.75

            XSOQHIST    SYS    0.75

            STREAMS    SYS    0.5

             

            below are some table from sysaux tablespace according to note which table i nned to move ??

             

            or i need to move all the tables.last column is size in MB.

                                                                                      SIZE IN MB

            STATS$SQL_SUMMARY    SYSAUX    253865.5    286.0703125

            WRI$_OPTSTAT_HISTHEAD_HISTORY    SYSAUX    37184.51    70.9140625

            WRI$_OPTSTAT_HISTGRM_HISTORY    SYSAUX    46324.88    48.171875

            WRH$_SQL_PLAN    SYSAUX    2775.6    47.1875

            STATS$EVENT_HISTOGRAM    SYSAUX    28554.09    34.390625

            STATS$LATCH    SYSAUX    21841.46    26.515625

            WRH$_SYSMETRIC_SUMMARY    SYSAUX    3575.65    23.5625

            WRI$_ADV_OBJECTS    SYSAUX    9076.87    23.5625

            STATS$SYSSTAT    SYSAUX    16701.92    19.625

            STATS$FILE_HISTOGRAM    SYSAUX    13477.71    17.65625

            STATS$PARAMETER    SYSAUX    12410.63    14.703125

            STATS$LATCH_MISSES_SUMMARY    SYSAUX    11811.56    13.71875

            WRH$_SQLTEXT    SYSAUX    213.09    12.734375

            STATS$SHARED_POOL_ADVICE    SYSAUX    777.35    11.75

            WRH$_ENQUEUE_STAT    SYSAUX    1524.22    10.765625

            STATS$PGA_TARGET_ADVICE    SYSAUX    623.7    8.796875

            STATS$SYSTEM_EVENT    SYSAUX    5998.58    7.8125

            STATS$SQL_WORKAREA_HISTOGRAM    SYSAUX    509.14    6.828125

            STATS$FILESTATXS    SYSAUX    5772.59    6.828125

            WRH$_SEG_STAT_OBJ    SYSAUX    175.12    6.828125

            STATS$SQLTEXT    SYSAUX    4009.53    4.90625

            STATS$ENQUEUE_STATISTICS    SYSAUX    3369.19    4.859375

            WRI$_OPTSTAT_IND_HISTORY    SYSAUX    2640.33    4.859375

            WRI$_ADV_MESSAGE_GROUPS    SYSAUX    2490.19    4.859375

            WRH$_BG_EVENT_SUMMARY    SYSAUX    542.67    3.875

            WRI$_ADV_PARAMETERS    SYSAUX    2637.03    3.875

            STATS$PROCESS_MEMORY_ROLLUP    SYSAUX    3132.42    3.875

            WRI$_ADV_ACTIONS    SYSAUX    2719.25    3.875

            STATS$BG_EVENT_SUMMARY    SYSAUX    1757.91    2.890625

            STATS$ROWCACHE_SUMMARY    SYSAUX    2517.54    2.890625

            WRH$_SHARED_POOL_ADVICE    SYSAUX    324.55    2.890625

            WRH$_PGASTAT    SYSAUX    285.47    2.890625

            WRH$_SQL_BIND_METADATA    SYSAUX    567.73    2.890625

            WRH$_SQL_WORKAREA_HISTOGRAM    SYSAUX    282.02    2.890625

            STATS$DB_CACHE_ADVICE    SYSAUX    1260.12    1.90625

            WRH$_TABLESPACE_SPACE_USAGE    SYSAUX    149.22    1.90625

            WRH$_PGA_TARGET_ADVICE    SYSAUX    261.13    1.90625

            STATS$SNAPSHOT    SYSAUX    85    1.90625

            WRI$_ADV_RATIONALE    SYSAUX    1532.62    1.90625

            WRH$_STREAMS_POOL_ADVICE    SYSAUX    231.29    1.90625

            WRH$_LIBRARYCACHE    SYSAUX    229.8    1.90625

            STATS$SGASTAT    SYSAUX    920.51    1.90625

            STATS$ROLLSTAT    SYSAUX    941.91    1.90625

            WRI$_OPTSTAT_TAB_HISTORY    SYSAUX    1152.78    1.90625

            WRI$_SEGADV_OBJLIST    SYSAUX    97.56    1.90625

            STATS$PROCESS_ROLLUP    SYSAUX    1590.5    1.90625

            WRI$_ADV_RECOMMENDATIONS    SYSAUX    397.1    0.921875

            WRH$_SGA_TARGET_ADVICE    SYSAUX    110.42    0.859375

            STATS$PGASTAT    SYSAUX    659.18    0.8046875

            STATS$MUTEX_SLEEP    SYSAUX    705.52    0.8046875

            WRH$_RESOURCE_LIMIT    SYSAUX    108.18    0.8046875

            WRH$_PROCESS_MEMORY_SUMMARY    SYSAUX    102.96    0.7421875

            WRH$_UNDOSTAT    SYSAUX    105.2    0.7421875

            STATS$WAITSTAT    SYSAUX    556.42    0.6875

            WRI$_ADV_FINDINGS    SYSAUX    342.54    0.6875

            STATS$LIBRARYCACHE    SYSAUX    540.05    0.6875

            STATS$STREAMS_POOL_ADVICE    SYSAUX    545.51    0.6875

            STATS$SYS_TIME_MODEL    SYSAUX    483.68    0.625

            STATS$OSSTAT    SYSAUX    376.4    0.5078125

            WRH$_LOG    SYSAUX    69.39    0.5078125

            WRI$_ADV_SQLT_PLANS    SYSAUX    0    0.5078125

            WRH$_SGA    SYSAUX    52.23    0.453125

            WRI$_ADV_REC_ACTIONS    SYSAUX    145.91    0.453125

            WRH$_BUFFER_POOL_STATISTICS    SYSAUX    46.26    0.3359375

            WRH$_RULE_SET    SYSAUX    32.76    0.3359375

            STATS$SGA_TARGET_ADVICE    SYSAUX    283.66    0.3359375

            STATS$UNDOSTAT    SYSAUX    281.79    0.3359375

            STATS_TARGET$    SYSAUX    122.42    0.3359375

            WRI$_ADV_TASKS    SYSAUX    268.67    0.3359375

            WRH$_SESS_TIME_STATS    SYSAUX    36.56    0.2734375

            WRH$_COMP_IOSTAT    SYSAUX    20.52    0.2734375

            STATS$RESOURCE_LIMIT    SYSAUX    234.23    0.2734375

            WRM$_SNAPSHOT    SYSAUX    21.64    0.21875

            WRH$_BUFFERED_SUBSCRIBERS    SYSAUX    15.59    0.21875

            STATS$TEMP_HISTOGRAM    SYSAUX    130.92    0.21875

            WRH$_JAVA_POOL_ADVICE    SYSAUX    26.86    0.21875

            WRH$_INSTANCE_RECOVERY    SYSAUX    27.61    0.21875

            WRH$_TEMPSTATXS    SYSAUX    26.49    0.21875

            WRH$_SQL_SUMMARY    SYSAUX    12.68    0.15625

            STATS$SGA    SYSAUX    130.92    0.15625

            WRH$_THREAD    SYSAUX    15.29    0.15625

            STATS$BUFFER_POOL_STATISTICS    SYSAUX    109.1    0.15625

            STATS$JAVA_POOL_ADVICE    SYSAUX    65.46    0.1015625

            STATS$SQL_STATISTICS    SYSAUX    35.46    0.1015625

            STATS$INSTANCE_RECOVERY    SYSAUX    44.55    0.1015625

            STATS$TEMPSTATXS    SYSAUX    90.92    0.1015625

            STATS$THREAD    SYSAUX    37.28    0.1015625

            STATS$RULE_SET    SYSAUX    69.16    0.1015625

            WRH$_BUFFERED_QUEUES    SYSAUX    3.09    0.1015625

            WRH$_EVENT_NAME    SYSAUX    58.23    0.1015625

            WRH$_PARAMETER_NAME    SYSAUX    52.59    0.1015625

            STATS$DATABASE_INSTANCE    SYSAUX    0.06    0.0390625

            WM$HINT_TABLE    SYSAUX    1.81    0.0390625

            LOGSTDBY$SKIP_SUPPORT    SYSAUX    1.12    0.0390625

            WRH$_METRIC_NAME    SYSAUX    17.6    0.0390625

            WRH$_SERVICE_NAME    SYSAUX    0.09    0.0390625

            WRI$_SCH_CONTROL    SYSAUX    0.03    0.0390625

            WRI$_SCH_VOTES    SYSAUX    0.05    0.0390625

            WRM$_DATABASE_INSTANCE    SYSAUX    7.13    0.0390625

            WRM$_SNAP_ERROR    SYSAUX    0    0.0390625

            WRM$_WR_CONTROL    SYSAUX    0.08    0.0390625

            WRI$_ADV_SQLT_BINDS    SYSAUX    0    0.0390625

            WRI$_ADV_SQLT_STATISTICS    SYSAUX    0    0.0390625

            WRI$_ADV_SQLT_RTN_PLAN    SYSAUX    0    0.0390625

            MGMT_BSLN_METRICS    SYSAUX    0.44    0.0390625

            WM$WORKSPACES_TABLE    SYSAUX    0.05    0.0390625

            WM$NEXTVER_TABLE    SYSAUX    0.01    0.0390625

            WM$VERSION_HIERARCHY_TABLE    SYSAUX    0.01    0.0390625

            WM$WORKSPACE_PRIV_TABLE    SYSAUX    0.18    0.0390625

            WM$ENV_VARS    SYSAUX    0.06    0.0390625

            WM$SYSPARAM_ALL_VALUES    SYSAUX    0.92    0.0390625

            AQ$_WM$EVENT_QUEUE_TABLE_S    SYSAUX    0.12    0.0390625

            WM$EVENTS_INFO    SYSAUX    0.28    0.0390625

            STATS$IDLE_EVENT    SYSAUX    1.78    0.0390625

            AQ$_SYS$SERVICE_METRICS_TAB_S    SYSAUX    0.21    0.0390625

            STATS$STATSPACK_PARAMETER    SYSAUX    0.08    0.0390625

            STATS$LEVEL_DESCRIPTION    SYSAUX    0.73    0.0390625

            STATS$TIME_MODEL_STATNAME    SYSAUX    0.65    0.0390625

            STATS$BUFFERED_QUEUES    SYSAUX    7.36    0.0390625

            STATS$BUFFERED_SUBSCRIBERS    SYSAUX    26.05    0.0390625

            STATS$OSSTATNAME    SYSAUX    0.26    0.0390625

            WRI$_ADV_DEFINITIONS    SYSAUX    0.2    0.0390625

            WRI$_ADV_DEF_PARAMETERS    SYSAUX    12.47    0.0390625

            WRI$_ADV_USAGE    SYSAUX    0.1    0.0390625

            WRI$_SEGADV_CNTRLTAB    SYSAUX    0.28    0.0390625

            WRI$_OPTSTAT_AUX_HISTORY    SYSAUX    0    0.0390625

            WRI$_OPTSTAT_OPR    SYSAUX    4.32    0.0390625

            SCHEDULER$_EVENT_LOG    SYSAUX    9.13    0.0390625

            SCHEDULER$_JOB_RUN_DETAILS    SYSAUX    8.78    0.0390625

            SCHEDULER$_WINDOW_DETAILS    SYSAUX    1.19    0.0390625

            WRI$_DBU_FEATURE_USAGE    SYSAUX    13.11    0.0390625

            WRI$_DBU_FEATURE_METADATA    SYSAUX    15.38    0.0390625

            WRI$_DBU_HIGH_WATER_MARK    SYSAUX    1.92    0.0390625

            WRI$_DBU_HWM_METADATA    SYSAUX    2.47    0.0390625

            WRI$_DBU_USAGE_SAMPLE    SYSAUX    0.13    0.0390625

            WRI$_DBU_CPU_USAGE    SYSAUX    0.91    0.0390625

            WRI$_DBU_CPU_USAGE_SAMPLE    SYSAUX    0.13    0.0390625

            WRI$_ALERT_OUTSTANDING    SYSAUX    0.34    0.0390625

            WRI$_ALERT_HISTORY    SYSAUX    0    0.0390625

            AQ$_ALERT_QT_S    SYSAUX    0.17    0.0390625

            WRI$_ALERT_THRESHOLD    SYSAUX    0.05    0.0390625

            WRH$_DATAFILE    SYSAUX    8.11    0.0390625

            WRH$_TEMPFILE    SYSAUX    0.06    0.0390625

            WRH$_OPTIMIZER_ENV    SYSAUX    1.57    0.0390625

            WRH$_LATCH_NAME    SYSAUX    15.51    0.0390625

            WRH$_STAT_NAME    SYSAUX    15.94    0.0390625

            WRH$_OSSTAT_NAME    SYSAUX    0.39    0.0390625

            AW_OBJ$    SYSAUX    0    0

            AW_PROP$    SYSAUX    0    0

            STREAMS$_APPLY_PROGRESS    SYSAUX    0    0

            APPLY$_ERROR    SYSAUX    0    0

            STREAMS$_APPLY_SPILL_TXN    SYSAUX    0    0

            STREAMS$_APPLY_SPILL_TXN_LIST    SYSAUX    0    0

            TSM_SRC$    SYSAUX    0    0

            TSM_DST$    SYSAUX    0    0

            DIR$MIGRATE_OPERATIONS    SYSAUX    0    0

            DIR$SERVICE_OPERATIONS    SYSAUX    0    0

            DIR$ESCALATE_OPERATIONS    SYSAUX    0    0

            DIR$QUIESCE_OPERATIONS    SYSAUX    0    0

            DIR$INSTANCE_ACTIONS    SYSAUX    0    0

            DIR$RESONATE_OPERATIONS    SYSAUX    0    0

            DIR$ALERT_HISTORY    SYSAUX    0    0

            DIR$REASON_STRINGS    SYSAUX    0    0

            DIR$DATABASE_ATTRIBUTES    SYSAUX    0    0

            DIR$VICTIM_POLICY    SYSAUX    0    0

            DIR$NODE_ATTRIBUTES    SYSAUX    0    0

            DIR$SERVICE_ATTRIBUTES    SYSAUX    0    0

            RECO_SCRIPT$    SYSAUX    0    0

            RECO_SCRIPT_PARAMS$    SYSAUX    0    0

            RECO_SCRIPT_BLOCK$    SYSAUX    0    0

            RECO_SCRIPT_ERROR$    SYSAUX    0    0

            WRI$_ADV_DIRECTIVES    SYSAUX    0    0

            WRI$_ADV_JOURNAL    SYSAUX    0    0

            WRI$_ADV_SQLW_SUM    SYSAUX    0    0

            WRI$_ADV_SQLW_STMTS    SYSAUX    0    0

            WRI$_ADV_SQLW_TABLES    SYSAUX    0    0

            WRI$_ADV_SQLW_TABVOL    SYSAUX    0    0

            WRI$_ADV_SQLW_COLVOL    SYSAUX    0    0

            WRI$_ADV_SQLA_MAP    SYSAUX    0    0

            WRI$_ADV_SQLA_STMTS    SYSAUX    0    0

            WRI$_ADV_SQLA_TMP    SYSAUX    0    0

            WRI$_ADV_SQLA_FAKE_REG    SYSAUX    0    0

            SYS_IOT_OVER_4478    SYSAUX    0    0

            SYS_IOT_OVER_4484    SYSAUX    0    0

            SYS_IOT_OVER_4488    SYSAUX    0    0

            SCHEDULER$_STEP_STATE    SYSAUX    0    0

            LOGMNR_SESSION_EVOLVE$    SYSAUX    0    0

            LOGMNR_HEADER1$    SYSAUX    0    0

            LOGMNR_HEADER2$    SYSAUX    0    0

            LOGMNR_UID$    SYSAUX    0    0

            LOGMNRC_DBNAME_UID_MAP    SYSAUX    0    0

            LOGMNRP_CTAS_PART_MAP    SYSAUX    0    0

            LOGMNR_LOG$    SYSAUX    0    0

            LOGMNR_PROCESSED_LOG$    SYSAUX    0    0

            LOGMNR_SPILL$    SYSAUX    0    0

            LOGMNR_AGE_SPILL$    SYSAUX    0    0

            LOGMNR_RESTART_CKPT_TXINFO$    SYSAUX    0    0

            • 3. Re: Purging SYSAUX table space
              Hemant K Chitale

              STATS$SQL_SUMMARY    is in the PERFSTAT schema.  You can export the PERFSTAT schema, drop it, resize the datafile and then reimport PERFSTAT.

               

              Hemant K Chitale

              • 4. Re: Purging SYSAUX table space
                user12009184

                hi,

                 

                does it having any impact if we do without any downtime any harm to database.

                 

                Thanks,

                • 5. Re: Purging SYSAUX table space
                  Hemant K Chitale

                  No, rebuilding PERFSTAT has no impact on the database.  If you have a scheduled job running statspack.snap, you need to disable it when the schema is being rebuilt.

                   

                  Hemant K Chitale

                  • 6. Re: Purging SYSAUX table space
                    user12009184

                    hi,

                     

                    even after doing above rebuild schema none of space got released.

                    so what we need to do to reallocate the space.

                     

                    Thanks,

                    • 7. Re: Purging SYSAUX table space
                      Hemant K Chitale

                      As I mentioned earlier see Oracle Support article "How to Reduce SYSAUX Tablespace Occupancy Due to Fragmented TABLEs and INDEXes [ID 1271178.1]"

                       

                      It is likely that non-PERFSTAT objects are at the HighWaterMark end of the datafile, preventing you from resizing the datafile.

                       

                      Hemant K Chitale


                      • 8. Re: Purging SYSAUX table space
                        Karan

                        Please read the note what hemant has mentioned and You can reduce the size of sysaux by letting oracle purge few objects like for example check space for SMB

                         

                        select space_usage_kbytes from v$sysaux_occupants where occupant_name = 'SQL_MANAGEMENT_BASE';

                         

                        then exec dbms_spm.configure('plan_retention_weeks','4');

                        increase the quota exec dbms_spm.configure('space_budget_percent','50');

                         

                        or by using DBMS_SPM.DROP_SQL_PLAN_BASELINE to delete baselines of your choice found in DBA_SQL_PLAN_BASELINES.

                         

                        Please do not move XDB objects out of SYSAUX individually through ALTER TABLE command as it is NOT SUPPORTED, it causes corruption in XDB.

                         

                        Document 1271560.1 Moving XDB Tablespace Using dbms_xdb.movexdb_tablespace or dbms_xdb_admin.movexdb_tablespace In An 11g Database.

                         

                        Regards

                        Karan

                        • 9. Re: Purging SYSAUX table space
                          user12009184

                          hi karan,

                           

                           

                          we didint get any output when we fire command as you given select space_usage_kbytes from v$sysaux_occupants where occupant_name = 'SQL_MANAGEMENT_BASE';

                          we get below out put when fire select * from v$sysaux_occupants

                           

                          OCCUPANT_NAMEOCCUPANT_DESCSCHEMA_NAMESPACE_USAGE_KBYTES
                          STATSPACKStatspack RepositoryPERFSTAT862976
                          SM/AWRServer Manageability - Automatic Workload RepositorySYS433536
                          SM/OPTSTATServer Manageability - Optimizer Statistics HistorySYS428288
                          SM/ADVISORServer Manageability - Advisor FrameworkSYS109888
                          WMWorkspace ManagerWMSYS7296
                          SM/OTHERServer Manageability - Other ComponentsSYS6848
                          LOGMNRLogMinerSYSTEM6080
                          EM_MONITORING_USEREnterprise Manager Monitoring UserDBSNMP1600
                          LOGSTDBYLogical StandbySYSTEM896
                          AOAnalytical Workspace Object TableSYS768
                          XSOQHISTOLAP API History TablesSYS768
                          STREAMSOracle StreamsSYS512
                          JOB_SCHEDULERUnified Job SchedulerSYS384
                          TSMOracle Transparent Session Migration UserTSMSYS256

                           

                           

                          please suggest what we need to do ?

                           

                           

                          thanks,

                          • 10. Re: Purging SYSAUX table space
                            Karan

                            In this case may be you are not using SMB or your version is old as you didnt mention your DB version..

                            I strongly recommend you to contact orace support first and ask them before doing anything, I can give few suggestions like following:=

                             

                            It can be a Bug number 8553944 - SYSAUX tablespace grows

                             

                            To reclaim space you may try other alternates like :=

                             

                            1) Removing snapshots with dbms_Workload_repository.drop_snapshot_range

                            2) Removing very old stats which are not needed with exec dbms_stats.purge_stats(sysdate-&days);

                            3) May be your sysaux is fragmented badly, check out which segments should be defragmented or may be you can move segments in a FRESH new tablespace like ALTER TABLE wri$_optstat_histgrm_history MOVE TABLESPACE SYSAUX_NEW_TEMP ;  where SYSAUX_NEW_TEMP is a fresh locally managed tablespace with segment space management AUTO.

                             

                            Or may be find an index which can be rebuilt by ALTER INDEX i_wri$_optstat_h_st REBUILD TABLESPACE sysaux;

                             

                            But be careful with objects having LONG or LONG RAW, you cannot push them to a different tablespace if im correct.

                             

                            Regards

                            Karan

                            • 11. Re: Purging SYSAUX table space
                              user12009184

                              our database version is 10.2.0.4.

                               

                               

                              thanks,

                              • 12. Re: Purging SYSAUX table space
                                Karan

                                You are welcome, and its 10G so forget SMB, its not there at all