WRH$_ACTIVE_SESSION_HISTORY

一萬年沒寫部落格了。。。

清理sysaux 裡的WRH$_ACTIVE_SESSION_HISTORY 

WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (文件 ID 387914.1)

sql>
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; 
/

------------得出結果----------------------
PARTITION NAME SNAP_ID DBID
--------------------------- ------- ----------
WRH$_ACTIVE_1647745226_0 Min 1 1647745226
WRH$_ACTIVE_1647745226_0 Max 15532 1647745226
---


sql>exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id =>1,high_snap_id =>15500);

sql>alter table WRH$_ACTIVE_SESSION_HISTORY shrink space;