News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Managing space in the PERFSTAT tablespace

  • Posted by Gavin Soorma
  • On June 27, 2009
  • 0 Comments
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 ;
 

0 Comments

Leave Reply

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