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.