1.創建PACKAGE:
CREATE OR REPLACE PACKAGE statspack_admin AS
PROCEDURE 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.
DECLARE
j BINARY_INTEGER;
BEGIN
DBMS_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 AS
on_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) IS
dbid 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;
BEGIN
SELECT 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_name
FROM v$database d, v$instance i;
select snap_time
into btime
from stats$snapshot b
where b.snap_id = (SELECT MIN(x.snap_id)
FROM stats$snapshot x
WHERE x.snap_id >= lo_snap)
and b.dbid = dbid
and b.instance_number = inst_num;
select snap_time
into etime
from stats$snapshot e
where e.snap_id = (SELECT MAX(x.snap_id)
FROM stats$snapshot x
WHERE x.snap_id <= hi_snap)
and e.dbid = dbid
and e.instance_number = inst_num;
IF on_9i AND area_size IS NOT NULL THEN
EXECUTE IMMEDIATE 'alter session set workarea_size_policy=MANUAL';
EXECUTE IMMEDIATE 'alter session set hash_area_size=' ||
TO_CHAR(area_size);
EXECUTE IMMEDIATE 'alter session set sort_area_size=' ||
TO_CHAR(area_size);
ELSIF area_size IS NOT NULL THEN
EXECUTE IMMEDIATE 'alter session set hash_area_size=' ||
TO_CHAR(area_size);
EXECUTE IMMEDIATE 'alter session set sort_area_size=' ||
TO_CHAR(area_size);
END IF;
delete from stats$snapshot
where instance_number = inst_num
and dbid = dbid
and snap_id between lo_snap and hi_snap;
/*-- Delete any dangling SQLtext
-- The following statement deletes any dangling SQL statements which
-- are no longer referred to by ANY snapshots. This statment has been
-- commented out as it can be very resource intensive.
--*/
delete -- index_ffs(st)
from stats$sqltext st
where (hash_value, text_subset) not in
(select -- hash_aj full(ss) no_expand
hash_value, text_subset
from stats$sql_summary ss
where ((snap_id < lo_snap or snap_id > hi_snap) and dbid = dbid and
instance_number = inst_num)
or (dbid != dbid or instance_number != inst_num));
-- Adding an optional STATS$SEG_STAT_OBJ delete statement
delete -- index_ffs(sso)
from stats$seg_stat_obj sso
where (dbid, dataobj#, obj#) not in
(select -- hash_aj full(ss) no_expand
dbid, dataobj#, obj#
from stats$seg_stat ss
where ((snap_id < lo_snap or snap_id > hi_snap) and dbid = dbid and
instance_number = inst_num)
or (dbid != dbid or instance_number != inst_num));
/* Delete any undostat rows that cover the snap times */
delete from stats$undostat us
where dbid = dbid
and instance_number = inst_num
and begin_time < btime
and end_time > etime;
/* Delete any dangling database
nstance rows for that startup time */
delete from stats$database_instance di
where instance_number = inst_num
and dbid = dbid
and not exists (select 1
from stats$snapshot s
where s.dbid = di.dbid
and s.instance_number = di.instance_number
and s.startup_time = di.startup_time);
/* Delete any dangling statspack parameter
rows for the database instance */
delete from stats$statspack_parameter sp
where instance_number = inst_num
and dbid = dbid
and not exists
(select 1
from stats$snapshot s
where s.dbid = sp.dbid
and 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) IS
ts DATE := SYSDATE;
te DATE := ts(window_in_hours / 24);
BEGIN
-- do the tables that haven't been rebuilt recently first
FOR t IN (SELECT object_name AS table_name
FROM user_objects
WHERE object_type = 'TABLE'
ORDER BY last_ddl_time ASC) LOOP
EXECUTE 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) LOOP
EXECUTE IMMEDIATE 'ALTER INDEX ' || i.index_name || '
REBUILD TABLESPACE ' || tablespace_name;
END LOOP;
IF SYSDATE > te THEN
EXIT;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
IF dbms_job.is_jobq THEN
/*
remove the job preventing nasty
reruns outside the window
but generating a trace file for the DBA
*/
DBMS_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) IS
losnap stats$snapshot.SNAP_ID%TYPE;
hisnap stats$snapshot.SNAP_ID%TYPE;
BEGIN
SELECT s.snap_id
INTO hisnap
FROM stats$snapshot s
WHERE s.snap_id =
(SELECT MAX(s.snap_id)
FROM stats$snapshot s
WHERE s.snap_time < TRUNC(SYSDATE) - days);
SELECT s.snap_id
INTO losnap
FROM stats$snapshot s
WHERE s.snap_id = (SELECT MIN(s.snap_id)
FROM stats$snapshot s
WHERE s.snap_id <= hisnap);
-- in case the highest snap is the only one to delete
--DBMS_OUTPUT.PUT_LINE('Lo Snap: '||TO_CHAR(losnap));
--DBMS_OUTPUT.PUT_LINE('Hi Snap: '||TO_CHAR(hisnap));
purge(losnap, hisnap, area_size);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
-- select into hisnap returned no rows,
--which means no snaps are older than days specified
END purge_older_than_days;
BEGIN
SYS.DBMS_UTILITY.DB_VERSION(dbversion, dbcompat);
IF TO_NUMBER(REPLACE(dbversion, '.')) >= 90000 THEN
-- we are on a 9i DB
on_9i := TRUE;
ELSE
on_9i := FALSE;
END IF;
EXCEPTION
WHEN OTHERS THEN
on_9i := FALSE;
END statspack_admin;
/
2.排程:
DECLARE
j BINARY_INTEGER;
BEGIN
DBMS_JOB.SUBMIT(j,
'statspack_admin.purge_older_than_days
(30, 52428800);',
TRUNC(SYSDATE) (3 / 24),
'TRUNC(SYSDATE) 1 (3/24)');
COMMIT;
END;