You can create the following procedure in your perfstat schema if you want to delete
the old statistics and rebuild the objects to free up the space in PERFSTAT tablespace.
create or replace procedure perfstat_freespace is
cursor c1 is
SELECT table_name from user_tables ;
cursor c2 is
SELECT index_name from user_indexes ;
l_str varchar2(200) ;
begin
delete from stats$snapshot where snap_time < sysdate - 10 ;
commit ;
for i in c1 loop
l_str := 'alter table '||i.table_name||' move ' ;
execute immediate l_str ;
end loop ;
for i in c2 loop
l_str := 'alter index '||i.index_name||' rebuild ' ;
execute immediate l_str ;
end loop ;
end ;
You can submit a job to execute the procedure every 10 day to delete the old statistics :
declare
jobno number;
begin
dbms_job.submit(:jobno, ' perfstat_freespace ;',
trunc(sysdate+1) + 6/24, 'sysdate + 1');
commit ;
end ;
The following two tabs change content below.
Gavin Soorma
