[20230203]完善awr.sql脚本.txt

[20230203]完善awr.sql脚本.txt --//来自WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy. --//主要原因是执行很慢,如果WRH$_ACTIVE_SESSION_HISTORY表很大的情况下. --//自己改写如下: 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); &&1 TYPE partrec IS RECORD (snapid number, dbid number); &&2 TYPE partrec IS RECORD (snapid number); TYPE partlist IS TABLE OF partrec; Outlist partlist; begin &&1 dbms_output.put_line('PARTITION  NAME             SNAP_ID DBID'); &&2 dbms_output.put_line('PARTITION  NAME             SNAP_ID ');     dbms_output.put_line('--------------------------- ------------------');     for part in cur_part loop &&1     query1 := 'select min(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid'; &&2     query1 := 'select min(snap_id) from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||')';         execute immediate query1 bulk collect into OutList;         if OutList.count > 0 then             for i in OutList.first..OutList.last loop &&1             dbms_output.put_line(part.partition_name||' Min '||OutList(i).snapid||' '||OutList(i).dbid); &&2             dbms_output.put_line(part.partition_name||' Min '||OutList(i).snapid);             end loop;         end if; &&1     query2 := 'select max(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid'; &&2     query2 := 'select max(snap_id)  from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||')';         execute immediate query2 bulk collect into OutList;         if OutList.count > 0 then             for i in OutList.first..OutList.last loop &&1             dbms_output.put_line(part.partition_name||' Max '||OutList(i).snapid||' '||OutList(i).dbid); &&2             dbms_output.put_line(part.partition_name||' Max '||OutList(i).snapid);                 dbms_output.put_line('---');             end loop;         end if;     end loop; end; /