SYSAUX 테이블스페이스 정리 WRH$ Table

-- sysaux occupants

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

/

-- min, max snap id Search

set serveroutput on

    declare

    CURSOR cur_part IS

    SELECT partition_name from dba_tab_partitions

    WHERE table_name = 'WRH$_ACTIVE_SESSION_HISTORY';

    query1 varchar2(200);

    query2 varchar2(200);

    TYPE partrec IS RECORD (snapid number, dbid number);

    TYPE partlist IS TABLE OF partrec;

    Outlist partlist;

    begin

    dbms_output.put_line('PARTITION NAME SNAP_ID DBID');

    dbms_output.put_line('--------------------------- ------- ----------');

    for part in cur_part loop

    query1 := 'select min(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||')

    group by dbid';

    execute immediate query1 bulk collect into OutList;

    if OutList.count > 0 then

    for i in OutList.first..OutList.last loop

    dbms_output.put_line(part.partition_name||' Min '||OutList(i).snapid||' '||OutList(i).dbid);

    end loop;

    end if;

    query2 := 'select max(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') 

    group by dbid';

    execute immediate query2 bulk collect into OutList;

    if OutList.count > 0 then

    for i in OutList.first..OutList.last loop

    dbms_output.put_line(part.partition_name||' Max '||OutList(i).snapid||' '||OutList(i).dbid);

    dbms_output.put_line('---');

    end loop;

    end if;

    end loop;

    end;

    /

-- delete snapshot from min_num to max_num

exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE ( min_num, max_num);

-- WRH$ Table Name 

SELECT TABLE_NAME, COUNT(*)

FROM DBA_TAB_PARTITIONS

WHERE TABLE_NAME LIKE 'WRH$%'

AND TABLE_OWNER = 'SYS'

GROUP BY TABLE_NAME

ORDER BY 1;

-- shrink table

alter table WRH$_SYSTEM_EVENT               shrink space;

alter table WRH$_SQLSTAT                      shrink space;

alter table WRH$_LATCH                         shrink space;

alter table WRH$_PARAMETER                  shrink space;

alter table WRH$_ROWCACHE_SUMMARY      shrink space;

alter table WRH$_DB_CACHE_ADVICE          shrink space;

alter table WRH$_WAITSTAT                     shrink space;

alter table WRH$_SGASTAT                      shrink space;

alter table WRH$_DLM_MISC                     shrink space;

alter table WRH$_SERVICE_STAT                shrink space;

alter table WRH$_SEG_STAT                     shrink space;

alter table WRH$_ACTIVE_SESSION_HISTORY  shrink space;

alter table WRH$_EVENT_HISTOGRAM          shrink space;

alter table WRH$_MVPARAMETER               shrink space;

alter table WRH$_FILESTATXS                   shrink space;

alter table WRH$_INST_CACHE_TRANSFER    shrink space;

alter table WRH$_INTERCONNECT_PINGS      shrink space;

alter table WRH$_LATCH_CHILDREN            shrink space;

alter table WRH$_LATCH_MISSES_SUMMARY  shrink space;

alter table WRH$_LATCH_PARENT              shrink space;

alter table WRH$_OSSTAT                       shrink space;

alter table WRH$_SERVICE_WAIT_CLASS      shrink space;

alter table WRH$_SYSSTAT                      shrink space;

alter table WRH$_SYS_TIME_MODEL            shrink space;

alter table WRH$_TABLESPACE_STAT          shrink space;