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

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;

10g has statspack.purge to do this, for example: 

exec statspack.purge(trunc(sysdate - 7), true)