Full Tablespace (Statspack)
Scott Reynolds
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.
astalavista
sptrunc delete all stats
but doesn't remove user/tablespace for STATSPACK...
HTH
sybr...@hccnet.nl
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
Scott Reynolds
<sybr...@hccnet.nl> wrote in message
news:ol04c3lahe2acd2ru...@4ax.com...
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.
sybr...@hccnet.nl
<tcla...@hotmail.com> wrote:
1) 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.
2) 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.
Brian Peasland
If you ran sppurge.sql, then there should be space in the tables to hold
new snapshot data. And this script should be run regularly...which is
why AWR does this automatically for you in 10g. So do you still get the
ORA-1653 error after you have run sppurge script?
HTH,
Brian
--
===================================================================
Brian Peasland
d...@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
Brian Peasland
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.