Home » RDBMS Server » Server Administration » Tablespace reaches threshold (Oracle 10g,10.2.0.4.0 , Unix)
Tablespace reaches threshold [message #494478] Tue, 15 February 2011 01:40 Go to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member

Hi all,

How to free up the tablespace size when it reaches its threshold (Max limit)?


Say for example USERS & SYSAUX tablespace reaches above 90%.



Thanks,

Report message to a moderator

Re: Tablespace reaches threshold [message #494479 is a reply to message #494478] Tue, 15 February 2011 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add space the the percentage will decrease.

Regards
Michel

Report message to a moderator

Re: Tablespace reaches threshold [message #494483 is a reply to message #494479] Tue, 15 February 2011 03:10 Go to previous messageGo to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member


Your answer is not clear Michel.Dont we have to remove any files relavant to tablespace to free up the space with it.

Report message to a moderator

Re: Tablespace reaches threshold [message #494484 is a reply to message #494483] Tue, 15 February 2011 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your question is not clear, what do you want exactly?

Regards
Michel

Report message to a moderator

Re: Tablespace reaches threshold [message #494485 is a reply to message #494484] Tue, 15 February 2011 03:14 Go to previous messageGo to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member


my question is how to reduce the size of tablespace when it reaches 90% of its usage?

Report message to a moderator

Re: Tablespace reaches threshold [message #494486 is a reply to message #494478] Tue, 15 February 2011 03:26 Go to previous messageGo to next message
pokhraj_d
Messages: 117
Registered: December 2007
Senior Member
Hi,
You cannot reduce the size of the tablespace.
Only you have add the data files to the tablespaces to increase space.

Thanks-
P

Report message to a moderator

Re: Tablespace reaches threshold [message #494488 is a reply to message #494485] Tue, 15 February 2011 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
balaji1482 wrote on Tue, 15 February 2011 10:14


my question is how to reduce the size of tablespace when it reaches 90% of its usage?


First drop the objects that are in it. Is this acceptable?

Regards
Michel

Report message to a moderator

Re: Tablespace reaches threshold [message #494493 is a reply to message #494486] Tue, 15 February 2011 03:35 Go to previous messageGo to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member


Thanks Michel .I think no need to drop objects in it.Only we can add datafiles to increase space to tablespace.

Report message to a moderator

Re: Tablespace reaches threshold [message #494497 is a reply to message #494493] Tue, 15 February 2011 04:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.

Regards
Michel

Report message to a moderator

Re: Tablespace reaches threshold [message #495695 is a reply to message #494497] Tue, 22 February 2011 01:03 Go to previous messageGo to next message
famegaurav
Messages: 23
Registered: November 2010
Location: Delhi India
Junior Member
You need to check tablespace fragmentation , after fagmentation it should be shrink sum spaces otherwise you need to add some datafiles or increase the maxsize of tablespace with autoextend on

Thanks,
Gaurav

Report message to a moderator

Re: Tablespace reaches threshold [message #495737 is a reply to message #495695] Tue, 22 February 2011 02:43 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
Hi, Gaurav - in the Oracle context, "fragmentation" means that your segments are divided up into many extents distributed through the tablespace. In the bad old days of dictionary managed tablespaces, yes, it was occasionally necessary to reorganize objects into one large extent, and this reorganization would also reduce the segment to its optimal size. However, fragmentation is NEVER an issue with locally managed tablespaces, which we can assume are being used.

Report message to a moderator

Re: Tablespace reaches threshold [message #495909 is a reply to message #495737] Wed, 23 February 2011 04:19 Go to previous messageGo to next message
famegaurav
Messages: 23
Registered: November 2010
Location: Delhi India
Junior Member
Hi John,

My concern is to only reduce some space from big tables using
"Alter table tablename move " , because as per my understanding if we use this then it is possible there is some space will be reduce from the tablespace level and may be its go down to the 90% of usages. Adding some space in a tablespace is always a big deal in an organization if space is a big issue.

Please correct me if i am wrong at any point.

Thanks,
Gaurav

Report message to a moderator

Re: Tablespace reaches threshold [message #495913 is a reply to message #495909] Wed, 23 February 2011 04:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
"Alter table tablename move " , because as per my understanding if we use this then it is possible there is some space will be reduce from the tablespace level

As it may "increase" the space used (put the HWM in files at a higher level).

Regards
Michel

Report message to a moderator

Re: Tablespace reaches threshold [message #495915 is a reply to message #495909] Wed, 23 February 2011 04:39 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
I understand. I would normally say that ALTER TABLE...SHRINK SPACE is a better alternative to MOVE, less locking and it won't break your indexes. And as Michel says, it won't push file HWM up: Oracle describes a table shrink as an "in-place on-line" operation.

Report message to a moderator

Re: Tablespace reaches threshold [message #495921 is a reply to message #495915] Wed, 23 February 2011 04:49 Go to previous message
famegaurav
Messages: 23
Registered: November 2010
Location: Delhi India
Junior Member
Hi John/Michel,

Thank you very much for your valuable suggestions

Regards,
gaurav

Report message to a moderator

Previous Topic: TNS: packet checksum failure
Next Topic: how to spot which oracle packages are installed / licensed
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed May 08 11:18:22 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.