|
Oracle 9i:
首先，我们需要执行以下的sql，创建包：
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; |
Oracle 10g：
自带了此项功能：
10g has statspack.purge to do this, for example:
exec statspack.purge(trunc(sysdate - 7), true) | |
