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;
/
|
