On Tue, 14 Aug 2007 14:58:43 -0500, "Scott Reynolds"
<tclaw420_at_hotmail.com> 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?
>
>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.
>
>
- I'm not sure which statistics you refer to. If you mean the
statistics on the statspack schema itself: they will be just wrong
(delete doesn't update the statistics)
If you refer to statistics in other schemas: they won't be affected.
- I have never used sptrunc.sql and I can't verify from here.
There is deallocate switch with the truncate command. If you don't use
that clause the space willl remain allocated to the segment.
You would need to run dbms_space to see how much is free *inside* the
tables.
--
Sybrand Bakker
Senior Oracle DBA
Received on Tue Aug 14 2007 - 15:48:56 CDT
