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
