PERFSTAT (stats) tablespace cleanup

Freeing space on PERF_STAT tablespace, first purge and then move tablespaces for large objects. (tested on 9i and 10g)


@?/rdbms/admin/sppurge

==

conn system

grant resource to perfstat;

conn perfstat/perfstat

alter table STATS$SQL_SUMMARY move tablespace active_v2;
alter table STATS$SQL_SUMMARY move tablespace perf_stat;
alter table STATS$SQLTEXT move tablespace active_v2;
alter table STATS$SQLTEXT move tablespace perf_stat;
alter table STATS$EVENT_HISTOGRAM move tablespace active_v2;
alter table STATS$EVENT_HISTOGRAM move tablespace perf_stat;
alter table STATS$LATCH move tablespace active_v2;
alter table STATS$LATCH move tablespace perf_stat;

select index_name, status from user_indexes where status not like 'VALID';

alter index STATS$LATCH_PK rebuild;
alter index STATS$SQL_SUMMARY_PK rebuild;
alter index STATS$SQLTEXT_PK rebuild;
alter index STATS$EVENT_HISTOGRAM_PK rebuild;

select index_name, status from user_indexes where status not like 'VALID';

conn system
revoke resource from perfstat;