Re: Full Tablespace (Statspack)

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Tue, 14 Aug 2007 15:02:16 -0500
Message-ID: <46c1fe31$0$16299$88260bb3@free.teranews.com>

Scott Reynolds wrote:

> <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?

The statistics in Statspack have nothing to do with the optimizer statistics collected for your tables and indexes. So deleting the old snapshots will not chagne the stats on the 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.

You can export your PERFSTAT user....then drop the user and recreate it. Then import your dump of PERFSTAT. This will help reclaim all of the space.

Once you have reclaimed the space, regularly schedule a purge of old snapshots.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

-- 
Posted via a free Usenet account from http://www.teranews.com

Received on Tue Aug 14 2007 - 15:02:16 CDT