Home » RDBMS Server » Server Administration » How release space on tablespace PERFSTAT (9.2i)
How release space on tablespace PERFSTAT [message #276430] Thu, 25 October 2007 02:33 Go to next message
sailor_man
Messages: 4
Registered: October 2007
Location: POLAND
Junior Member
Hi,
I've problem with low space on tablespace PERFSTAT. I used @?/rdbms/admin/sppurge script to remove most of snaps.
Space on PERFSTAT wasn't release.
Some one know, how can i relese space on this tablespace?

I checked free space below query:
select sum(Bytes) from dba_free_space where tablespace_name = 'PERFSTAT'

This query showed the same values before and after remove snaps.

Thanks for all request.

[Updated on: Thu, 25 October 2007 02:52]

Report message to a moderator

Re: How release space on tablespace PERFSTAT [message #276432 is a reply to message #276430] Thu, 25 October 2007 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
delete (sppurge) doesn't give back space to tablespace.
sptrunc uses truncate and gives back space to tablespace but not to OS, you have to then resize the tablespace datafile(s) afterward (if PERFSTAT was the only user in this tablespace).

Regards

[Updated on: Thu, 25 October 2007 02:40]

Report message to a moderator

Re: How release space on tablespace PERFSTAT [message #276442 is a reply to message #276432] Thu, 25 October 2007 03:02 Go to previous messageGo to next message
sailor_man
Messages: 4
Registered: October 2007
Location: POLAND
Junior Member
Does sptrunc truncate all of the date from tablespace PERFSTAT?
I haven't space on table space PERFSTAT and i need truncate snap older than two weeks.
I will not to increase size of tablespace PERFSTAT.
Re: How release space on tablespace PERFSTAT [message #276459 is a reply to message #276442] Thu, 25 October 2007 04:03 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Yes, sptrunc delete all data.
you can use SPPURGE as mihead already suggested.
 Batch mode purging
      ~~~~~~~~~~~~~~~~~~
      To purge in batch mode, you must assign values to the SQL*Plus
      variables which specify the low and high snapshot Ids to purge.

      The variables are:
        losnapid   -> Begin Snapshot Id
        hisnapid   -> End Snapshot Id

      e.g.
          SQL>  connect perfstat/perfstat
          SQL>  define losnapid=1
          SQL>  define hisnapid=2
          SQL>  @sppurge

      sppurge will no longer prompt for the above information.


Just search on your system SPDOC.txt file for more information.
Re: How release space on tablespace PERFSTAT [message #276473 is a reply to message #276442] Thu, 25 October 2007 04:49 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you have to use sppurge and want your space get back, then you have to reorganize perfstats objects.
For instance, with alter table move, alter index rebuild or exp/trunc/imp...

Regards
Michel
Previous Topic: Help me to resolve this problem!
Next Topic: ORA-04030: out of process memory
Goto Forum:
  


Current Time: Wed Dec 07 22:07:44 CST 2016

Total time taken to generate the page: 0.13491 seconds