Re: Full Tablespace (Statspack)

<sybrandb_at_hccnet.nl> wrote in message
news:ol04c3lahe2acd2rububut5gso512j3knq_at_4ax.com...
> 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

I understand. A few more questions...

1. Will deleting old snapshots affect the statistics currently applied to the database tables?
2. Is there any way to reclaim the space once I truncate the tables? I did a test on another server and it still says the tablespace is 100% full (after sptrunc.sql) even though I am able to collect stats again. I would just like to know how much space is actually free in the tablespace.