This discussion is archived
2 Replies Latest reply: Jan 28, 2013 9:50 AM by jgarry RSS

How to clean up sysaux?

jgarry Guru
Currently Being Moderated
11g XE on XP Pro SP3. 8G sysaux. Followed MOS 1271178.1 and 396502.1 and created a temp_sysaux, moved largest things there, moved and rebuilt some things in the sysaux tablespace, now I'm looking at a whole bunch of partitioned things like (from http://jonathanlewis.wordpress.com/tablespace-hwm/):
   FILE_ID   BLOCK_ID  END_BLOCK OWNER                                                                                            
---------- ---------- ---------- ------------------------------                                                                   
SEGMENT_NAME                                                                      PARTITION_NAME                                  
--------------------------------------------------------------------------------- ------------------------------                  
SEGMENT_TYPE                                                                                                                      
------------------                                
...
42868717_1007    TABLE PARTITION
         2      90128      90135 SYS                            WRH$_EVENT_HISTO
GRAM_PK                                                           WRH$_EVENT__26
42868717_959     INDEX PARTITION
         2      90136      90143 SYS                            WRH$_SEG_STAT_PK
                                                                  WRH$_SEG_ST_26
42868717_1007    INDEX PARTITION
         2      90144      90151 SYS                            WRH$_MVPARAMETER
_PK                                                               WRH$_MVPARA_26
42868717_959     INDEX PARTITION
         2      90152      90159 free                           free
         2      90160      90167 SYS                            WRH$_SEG_STAT_PK
                                                                  WRH$_SEG_ST_26
42868717_983     INDEX PARTITION
         2      90168      90175 SYS                            WRH$_SEG_STAT
                                                                  WRH$_SEG_ST_26
42868717_983     TABLE PARTITION
         2      90176      90183 SYS                            WRH$_SEG_STAT
                                                                  WRH$_SEG_ST_26
42868717_983     TABLE PARTITION
         2      90184      90191 SYS                            WRH$_SEG_STAT
                                                                  WRH$_SEG_ST_26
42868717_1007    TABLE PARTITION
         2      90192      90199 SYS                            WRH$_SEG_STAT
                                                                  WRH$_SEG_ST_26
42868717_1055    TABLE PARTITION
         2      90200      90207 SYS                            WRH$_SEG_STAT_PK
                                                                  WRH$_SEG_ST_26
42868717_1055    INDEX PARTITION
         2      90208     117759 free                           free

6185 rows selected.
Even with all that free, I can't even shrink the data file down to 7G. Have I missed something obvious? Are there scripts to move all these partitions? Some way to turn off their generation?
  • 1. Re: How to clean up sysaux?
    Udo Guru
    Currently Being Moderated
    Hello Joel,

    these objects belong to the AWR... Though Automatic Workload Management is not available in XE, the statistics for the workload repository are generated by default and could be queried...
    To find out what kind of data they are supposed to store, you can run
    SELECT table_name_kewrtb, table_id_kewrtb, desc_kewrtb
      FROM x$kewrtb
     ORDER BY table_id_kewrtb;
    Are there scripts to move all these partitions?
    It should be safe to move the objects that belong to these tables to your temporary SYSAUX tablespace as well. For whatever reason, ID 1271178.1 only describes the actions necessary to move unpartitioned objects. If you have partitioned objects with local space management, their partitions will stay where they are when you move these objects, e. g. when you move an index to a different tablespace, only the "body" will be moved, locally managed partitions of that index will remain in their defined tablespace.
    To provide an example for your case, the partitions of the index WRH$_EVENT_HISTOGRAM_PK can be moved to your temp tablespace using the commands generated by the following query (as SYS)
    DEFINE tmp_tablespace=SYSAUX_TEMP
    SELECT 'ALTER INDEX '||index_name||' REBUILD PARTITION '||partition_name||' TABLESPACE &tmp_tablespace.;'
      FROM user_ind_partitions
     WHERE index_name='WRH$_EVENT_HISTOGRAM_PK';
    Some way to turn off their generation?
    You can manage snapshots and their settings using the DBMS_WORKLOAD_REPOSITORY package. Possibly dropping snapshots can help you reduce the amount of data near your SYSAUX HWM already and I'd recommend to redefine the AWR settings to fit your actual needs. But if you really need to reduce your SYSAUX size to the minimum you'll need to move all objects "above" your free space...

    -Udo
  • 2. Re: How to clean up sysaux?
    jgarry Guru
    Currently Being Moderated
    This wound up being hopeless. I tried disabling AWR (MOS 436386.1), then catnoawr plus another 8 WRH$ tables it seems to miss. Still no joy, then next tables are like wrm$_snapshot_details...

    See mos WRM$_SNAPSHOT_DETAILS Table is Not Purged [ID 1489801.1]

    The solutions are a big raspberry from Oracle.

    This still begs the question why no one else has seen this on XE, since these are all known bugs, but whatever. One can't help feeling the partitioned tables ignored by MOS 1271178.1 are simply too embarrassing to deal with. Actual AWR needs with XE are...?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points