如何STATSPACK定期清除1
Oracle 9i: 首先，我们需要执行以下的sql，创建包： CREATE OR REPLACE PACKAGE statspack_admin ASPROCEDURE purge_older_than_days (days IN INTEGER, area_size IN INTEGER DEFAULT NULL);/*-- submit a job to run every day at 3am deleting snaps older than 30 days-- specifying 50Mb for PGA。 DECLAREj BINARY_INTEGER;BEGINDBMS...全部
Oracle 9i: 首先，我们需要执行以下的sql，创建包： CREATE OR REPLACE PACKAGE statspack_admin ASPROCEDURE purge_older_than_days (days IN INTEGER, area_size IN INTEGER DEFAULT NULL);/*-- submit a job to run every day at 3am deleting snaps older than 30 days-- specifying 50Mb for PGA。
DECLAREj BINARY_INTEGER;BEGINDBMS_JOB。SUBMIT(j, 'statspack_admin。purge_older_than_days(30, 52428800);', TRUNC(SYSDATE)+(3/24), 'TRUNC(SYSDATE)+1+(3/24)' );COMMIT;END;*/PROCEDURE purge (lo_snap IN NUMBER, hi_snap IN NUMBER, area_size IN INTEGER DEFAULT NULL);PROCEDURE move_tablespace (tablespace_name IN VARCHAR2, window_in_hours IN NUMBER);END statspack_admin;/CREATE OR REPLACE PACKAGE BODY statspack_admin ASon_9i BOOLEAN := FALSE;dbversion VARCHAR2(512);dbcompat VARCHAR2(512);PROCEDURE purge (lo_snap IN NUMBER, hi_snap IN NUMBER, area_size IN INTEGER DEFAULT NULL)ISdbid v$database。
dbid%TYPE;inst_num v$instance。instance_number%TYPE;inst_name v$instance。instance_name%TYPE;db_name v$database。
name%TYPE;btime DATE;etime DATE;BEGINSELECT d。dbid AS dbid, i。instance_number AS inst_num, i。instance_name AS inst_name, d。
name AS db_name INTO dbid, inst_num, inst_name, db_nameFROM v$database d, v$instance i;select snap_timeinto btimefrom stats$snapshot bwhere b。
snap_id = (SELECT MIN(x。snap_id) FROM stats$snapshot x WHERE x。snap_id >= lo_snap) and b。dbid = dbidand b。
instance_number = inst_num;select snap_time into etimefrom stats$snapshot ewhere e。snap_id = (SELECT MAX(x。
snap_id) FROM stats$snapshot x WHERE x。snap_id hi_snap)and dbid = dbidand instance_number = inst_num)or ( dbid != dbidor instance_number != inst_num));-- Adding an optional STATS$SEG_STAT_OBJ delete statementdelete --+ index_ffs(sso)from stats$seg_stat_obj ssowhere (dbid, dataobj#, obj#) not in(select --+ hash_aj full(ss) no_expanddbid, dataobj#, obj#from stats$seg_stat sswhere ( ( snap_id hi_snap)and dbid = dbidand instance_number = inst_num)or ( dbid != dbidor instance_number != inst_num));/* Delete any undostat rows that cover the snap times */delete from stats$undostat uswhere dbid = dbidand instance_number = inst_numand begin_time etime;/* Delete any dangling database nstance rows for that startup time */delete from stats$database_instance diwhere instance_number = inst_numand dbid = dbidand not exists (select 1from stats$snapshot swhere s。
dbid = di。dbidand s。instance_number = di。instance_numberand s。startup_time = di。startup_time);/* Delete any dangling statspack parameter rows for the database instance */delete from stats$statspack_parameter spwhere instance_number = inst_numand dbid = dbidand not exists (select 1from stats$snapshot swhere s。
dbid = sp。dbidand s。instance_number = sp。instance_number);COMMIT;END purge;/* procedure to move tablespaces */PROCEDURE move_tablespace (tablespace_name IN VARCHAR2, window_in_hours IN NUMBER)ISts DATE := SYSDATE; te DATE := ts + (window_in_hours/24);BEGIN-- do the tables that haven't been rebuilt recently firstFOR t IN (SELECT object_name AS table_name FROM user_objects WHERE object_type = 'TABLE' ORDER BY last_ddl_time ASC) LOOPEXECUTE IMMEDIATE 'ALTER TABLE '||t。
table_name||' MOVE TABLESPACE '||tablespace_name;-- now immediately rebuild the indexes-- I could use dbms_job to do this asynchronously, maybe in a future revision?-- not using ONLINE because moving tables cannot be done ONLINE! -- maybe use dbms_redef in future version? FOR i IN (SELECT index_name FROM user_indexes WHERE table_name = t。
table_name) LOOPEXECUTE IMMEDIATE 'ALTER INDEX '||i。index_name||' REBUILD TABLESPACE '||tablespace_name;END LOOP;IF SYSDATE > te THENEXIT;END IF;END LOOP;EXCEPTIONWHEN OTHERS THENIF dbms_job。
is_jobq THEN -- remove the job preventing nasty reruns outside the window but generating a trace file for the DBADBMS_JOB。
REMOVE( NVL(SYS_CONTEXT('USERENV','BG_JOB_ID'),SYS_CONTEXT('USERENV','FG_JOB_ID') ) );COMMIT;END IF; RAISE;END move_tablespace;/* purge records older than X days */PROCEDURE purge_older_than_days (days IN INTEGER, area_size IN INTEGER DEFAULT NULL) ISlosnap stats$snapshot。
SNAP_ID%TYPE;hisnap stats$snapshot。SNAP_ID%TYPE;BEGINSELECTs。snap_idINTO hisnapFROMstats$snapshot sWHERE s。
snap_id =(SELECT MAX(s。snap_id)FROM stats$snapshot sWHERE s。snap_time = 90000 THEN -- we are on a 9i DB on_9i := TRUE;ELSEon_9i := FALSE;END IF; EXCEPTIONWHEN OTHERS THENon_9i := FALSE;END statspack_admin;/
2。
然后再继续手工执行： DECLAREj BINARY_INTEGER;BEGINDBMS_JOB。SUBMIT(j, 'statspack_admin。purge_older_than_days(30, 52428800);', TRUNC(SYSDATE)+(3/24), 'TRUNC(SYSDATE)+1+(3/24)' );COMMIT;END;
Oracle 10g： 自带了此项功能： 10g has statspack。
purge to do this, for example: exec statspack。purge(trunc(sysdate - 7), true。收起
