OraFAQ Forum: Server Administration » How release space on tablespace PERFSTAT

Home » RDBMS Server » Server Administration » How release space on tablespace PERFSTAT (9.2i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How release space on tablespace PERFSTAT [message #276430]

Thu, 25 October 2007 02:33

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

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.

Report message to a moderator

Re: How release space on tablespace PERFSTAT [message #276459 is a reply to message #276442]

Thu, 25 October 2007 04:03

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.

Report message to a moderator

Re: How release space on tablespace PERFSTAT [message #276473 is a reply to message #276442]

Thu, 25 October 2007 04:49

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 30 04:38:45 CST 2024