Recovering space of Statspack indexes

If the tablespace assigned to Statspack objects grows too much, you might try to recover space rebuilding some indexes and moving tables:

SQL> column SEGMENT_NAME format a50
SQL> select * from (select SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024 as MBYTES from dba_segments 
where OWNER='PERFSTAT' and TABLESPACE_NAME='STATSPACK' order by BYTES desc) where rownum < 10;

SEGMENT_NAME                                        SEGMENT_TYPE           MBYTES
--------------------------------------------------- ------------------ ----------
STATS$SQL_SUMMARY                                   TABLE                    1245
STATS$SQL_SUMMARY_PK                                INDEX                     240
STATS$PARAMETER_PK                                  INDEX                      42
STATS$SYSSTAT_PK                                    INDEX                      39
STATS$LATCH_PK                                      INDEX                      35
STATS$LATCH_MISSES_SUMMARY_PK                       INDEX                      34
STATS$LATCH                                         TABLE                      33
STATS$SYSSTAT                                       TABLE                      26
STATS$PARAMETER                                     TABLE                      25

9 rows selected.

SQL> ALTER table PERFSTAT.STATS$SQL_SUMMARY move;

Table altered.

SQL> ALTER INDEX PERFSTAT.STATS$SQL_SUMMARY_PK REBUILD ONLINE;

Index altered.

SQL> ALTER INDEX PERFSTAT.STATS$LATCH_PK REBUILD ONLINE;

Index altered.

SQL> ALTER INDEX PERFSTAT.STATS$LATCH_PARENT_PK REBUILD ONLINE;

Index altered.

SQL> ALTER INDEX PERFSTAT.STATS$LATCH_MISSES_SUMMARY_PK REBUILD ONLINE;

Index altered.

SQL> ALTER INDEX PERFSTAT.STATS$LATCH_CHILDREN_PK REBUILD ONLINE;

Index altered.

SQL> ALTER INDEX PERFSTAT.STATS$SYSSTAT_PK REBUILD ONLINE;

Index altered.

Sometimes the tables and indexes grows so much that it might be better to just truncate everything; if this is the case then you can use the sptrunc script:

SQL> alter session set current_schema=PERFSTAT;

Session altered.

SQL> @?/rdbms/admin/sptrunc

Warning
~~~~~~~
Running sptrunc.sql removes ALL data from Statspack tables.  You may
wish to export the data before continuing.


About to Truncate Statspack Tables
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If would like to exit WITHOUT truncating the tables, enter any text at the
begin_or_exit prompt (e.g. 'exit'), otherwise if you would like to begin
the truncate operation, press (return)


Enter value for begin_or_exit: 
Entered  at the 'begin_or_exit' prompt

Beginning truncate operation


Table truncated.


Table truncated.

...

Commit complete.


Package altered.


Truncate operation complete