|
This was adapted from SPPURGE.SQL for this very
purpose...
===============================================================
set echo on feedback on timing on verify
on
spool sppurpkg
connect perfstat
show user
show release set termout off
create or replace package SPPURPKG is -- procedure PURGE(in_days_older_than IN INTEGER); -- end SPPURPKG; / set termout on show errors set termout off
create or replace package body SPPURPKG is -- procedure PURGE(in_days_older_than IN INTEGER) is -- cursor get_snaps(in_days IN INTEGER) is select s.rowid, s.snap_id, s.dbid, s.instance_number from stats$snapshot s, sys.v_$database d, sys.v_$instance i where s.dbid = d.dbid and s.instance_number = i.instance_number and s.snap_time < trunc(sysdate) - in_days; -- errcontext VARCHAR2(100); errmsg VARCHAR2(1000); save_module VARCHAR2(48); save_action VARCHAR2(32); -- begin -- errcontext := 'save settings of DBMS_APPLICATION_INFO'; dbms_application_info.read_module(save_module, save_action); dbms_application_info.set_module('SPPURPKG.PURGE', 'begin'); -- errcontext := 'open/fetch get_snaps'; dbms_application_info.set_action(errcontext); for x in get_snaps(in_days_older_than) loop -- errcontext := 'delete (cascade) STATS$SNAPSHOT'; dbms_application_info.set_action(errcontext); delete from stats$snapshot where rowid = x.rowid; -- errcontext := 'delete "dangling" STATS$SQLTEXT rows'; dbms_application_info.set_action(errcontext); delete from stats$sqltext where (hash_value, text_subset) not in (select /*+ hash_aj(ss) */ hash_value, text_subset from stats$sql_summary ss ); -- errcontext := 'delete "dangling" STATS$DATABASE_INSTANCE rows'; dbms_application_info.set_action(errcontext); delete from stats$database_instance i where i.instance_number = x.instance_number and i.dbid = x.dbid and not exists (select 1 from stats$snapshot s where s.dbid = i.dbid and s.instance_number = i.instance_number and s.startup_time = i.startup_time ); -- errcontext := 'delete "dangling" STATS$STATSPACK_PARAMETER rows'; dbms_application_info.set_action(errcontext); delete from stats$statspack_parameter p where p.instance_number = x.instance_number and p.dbid = x.dbid and not exists (select 1 from stats$snapshot s where s.dbid = p.dbid and s.instance_number = p.instance_number ); -- errcontext := 'fetch/close get_snaps'; dbms_application_info.set_action(errcontext); -- end loop; -- errcontext := 'restore saved settings of DBMS_APPLICATION_INFO'; dbms_application_info.set_module(save_module, save_action); -- exception -- when OTHERS then errmsg := sqlerrm; dbms_application_info.set_module(save_module, save_action); raise_application_error(-20000, errcontext || ': ' || errmsg); -- end PURGE; -- end SPPURPKG; / set termout on show errors spool off
|
- STATSPACK PURGE Reddy, Madhusudana
- Tim Gorman
