I recently used statspack on a 8i production database of the customer and found that alert has an error:
Mon Jun 16 13:17:52 2014
Errors in file/oracle/8.1.7/admin/prod/bdump/snp0_96626_prod.trc:
ORA-12012: error on auto execute of job 304
ORA-01631: max # extents (505) reached in table PERFSTAT. STATS $ SQL _SUMMARY
ORA-06512: at "PERFSTAT. STATSPACK", line 978
ORA-06512: at "PERFSTAT. STATSPACK", line 1612
ORA-06512: at "PERFSTAT. STATSPACK", line 71
ORA-06512: at line 1
Mon Jun 16 13:47:46 2014
ORA-1631: max # extents 505 reached in table PERFSTAT. STATS $ SQL _SUMMARY
The solution is as follows:
SQL> select tablespace_name, bytes/1024/1024 from dba_free_space where tablespace_name = 'statspack1 ';
TABLESPACE_NAME BYTES/1024/1024
---------------------------------------------
STATSPACK1 63.484375
STATSPACK1 2047.99219
SQL> SELECT segment_name, owner, EXTENTS, max_extents, segment_type
2 FROM dba_segments
3 WHERE segment_name = 'stats $ SQL _SUMMARY ';
SEGMENT_NAME owner extents MAX_EXTENTS
-----------------------------------------------------------------------
SEGMENT_TYPE
------------------
STATS $ SQL _SUMMARY PERFSTAT 505 505
TABLE
SQL> ALTER table PERFSTAT. STATS $ SQL _SUMMARY STORAGE (MAXEXTENTS 8192 );
Table altered.
SQL> set linesize 1000
SQL> SELECT segment_name, owner, EXTENTS, max_extents, segment_type
2 FROM dba_segments
3 WHERE segment_name = 'stats $ SQL _SUMMARY ';
SEGMENT_NAME owner extents MAX_EXTENTS SEGMENT_TYPE
-----------------------------------------------------------------------------------------
STATS $ SQL _SUMMARY perfstat505 8192 TABLE