1. Home
  2. Knowledge Base
  3. Database Administration
  4. Managing space in the PERFSTAT tablespace

Managing space in the PERFSTAT tablespace

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

Latest posts by Gavin Soorma (see all)

Updated on June 2, 2021

Was this article helpful?

Related Articles

Leave a Comment