Loading....
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 ;
Please follow and like us:

Last Update: June 27, 2009  

June 27, 2009 145 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?