ANBOB™

1) Export the snapshots and then do a manual purge

To purge the Table SYS.WRH$_ACTIVE_SESSION_HISTORY, you may first query the Snapshot History
with the query below:

select snap_id, begin_interval_time from sys.dba_hist_snapshot order by snap_id;

Then, you choose the range of snapshot you want to delete, and you execute the following Procedure:

exec dbms_workload_repository.drop_snapshot_range ( low_snap_id => snap_id_1, high_snap_id=> snap_id_2 );
or
exec dbms_stats.purge_stats(sysdate-&days);

But, if the delete will free space inside the Table, it won’t release space to the SYSAUX Tablespace.

alter table sys.wrh$_active_session_history enable row movement;
alter table sys.wrh$_active_session_history shrink space cascade;
alter table sys.wrh$_active_session_history disable row movement;

2)change the Snapshot Settings and retention time for Automatic Workload Repository.
AWR tables are not being purged according to settings in sys.wrm$_wr_control. Because of this the tables are accumulating more and more rows and the segments associated with these tables become very large.

BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200,
interval => 30, topnsql => 100, dbid => xxxxx);
END;

Try to force the execution of the purge operations :
alter session set “_swrf_test_action” = 72;

3) Ignore this error Else let oracle do the job. Did you know the MMON does auto maintenance of sysaux storage management. Look below as taken from alert log.

ORA-1688: unable to extend table SYS.WRH$_LATCH_CHILDREN partition WRH$_LATCH__2530202403_28403 by 128 in tablespace SYSAUX
ORA-1688: unable to extend table SYS.WRH$_LATCH_CHILDREN partition WRH$_LATCH__2530202403_28403 by 128 in tablespace SYSAUX
MMON Flush encountered SYSAUX out of space error(1688).
MMON (emergency) purge of WR snapshots (28078) and older

4) Increase space of sysaux data file or add another file.
add a new datafile as follow:
alter tablespace SYSAUX add datafile ‘/xxxx/sysaux02.dbf’ size 100M autoextend on next 10M maxsize 2000M;

5) Oracle provides utlsyxsz.sql file to help calculate approx the size required by sysaux. Find out the space and add another 25% extra on top of it to be sure for a good measure.

6)WRH$_ACTIVE_SESSION_HISTORY is a partition table,Check how many partitions do exist for the offending table,we store the snapshot data into partitions. we only drop the partition if all the data in the partition has expired. if the partition contains at least one row which, according to the retention policy shouldn’t be removed, then the partition won’t be dropped and as such the table will contain old data.

select table_name,partition_name from dba_tab_partitions where table_name = ‘WRH$_ACTIVE_SESSION_HISTORY’;

run $ORACLE_HOME/admin/awrinfo.sql
Collect awrinfo report and verify which object is consuming the most space in the SYSAUXtablespace.

or
collect all tablespace statitics using follow sql script by Maclean

set pages 999
col tsname  format     a16 justify c heading 'Tablespace' 
col nfrags  format 999,990 justify c heading 'Free|Frags' 
col mxfrag  format 999,999 justify c heading 'Largest|Frag (MB)' 
col totsiz  format 999,999 justify c heading 'Total|(MB)' 
col avasiz  format 999,999 justify c heading 'Available|(MB)' 
col pctusd  format     990 justify c heading 'Pct|Used' 

select total.TABLESPACE_NAME tsname,
       D nfrags,
       C/1024/1024 mxfrag,
       A/1024/1024 totsiz,
       B/1024/1024 avasiz,
       (1-nvl(B,0)/A)*100 pctusd
from
    (select sum(bytes) A,
            tablespace_name
            from dba_data_files
            group by tablespace_name) TOTAL,
    (select sum(bytes) B,
            max(bytes) C,
            count(bytes) D, 
            tablespace_name
            from dba_free_space
            group by tablespace_name) FREE
where 
      total.TABLESPACE_NAME=free.TABLESPACE_NAME(+)
/

                   Free    Largest   Total   Available Pct
   Tablespace     Frags   Frag (MB)   (MB)     (MB)    Used
---------------- -------- --------- -------- --------- ----
IDX_ICME               71        24   17,754       528   97
UNDOTBS1               86     3,968   14,000    13,722    2
SYSAUX                 22        21      870       100   88
YDYF                    1       100      100       100    0
KEYAN                   2        72      100        73   28
USERS                  19     1,357   14,039     1,369   90
GGSDDL                  2        40      100        40   60
SYSTEM                  3       608    1,630     1,097   33
ICME                    4        30   33,001        31  100

打赏

微信扫一扫，打赏作者吧～