Re: Full Tablespace (Statspack)

On Tue, 14 Aug 2007 13:24:51 -0500, "Scott Reynolds" <tclaw420_at_hotmail.com> wrote:

>I am currently running Oracle 9i and unfortunately my STATSPACK tablespace
>has filled 100%. When collecting statistics now, I get the following error
>in my alert log:
>
>ORA-1653: unable to extend table PERFSTAT.STATS$SQLTEXT by 16 in tablespace
>STATSPACK
>
>
>This makes sense as I limited the datafile size to 1500 MB. I have also run
>the sppurge.sql script to remove old snapshots with the idea that it would
>free up enough space to continue collecting statistics. Unfortunately.
>
>Any ideas or will I have to run the sptrunc.sql script to remove STATSPACK
>and reinstall after dropping/recreating the STATSPACK tablspace?
>
>
>
>Thanks in advance.
>
>

Prior to running sptrunc you would better rebuild all indexes. They are in the same tablespace and get bigger quite rapidly, and it seems space is hardly reused, which is logical as the snapshot_id is always a column in the concatenated key.

-- 
Sybrand Bakker
Senior Oracle DBA