| Tablespace reaches threshold [message #494478] |
Tue, 15 February 2011 01:40  |
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,
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Tablespace reaches threshold [message #495695 is a reply to message #494497] |
Tue, 22 February 2011 01:03  |
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
|
|
|
|
| Re: Tablespace reaches threshold [message #495737 is a reply to message #495695] |
Tue, 22 February 2011 02:43 |
John Watson
Messages: 8964
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.
|
|
|
|
| Re: Tablespace reaches threshold [message #495909 is a reply to message #495737] |
Wed, 23 February 2011 04:19 |
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
|
|
|
|
|
|
| Re: Tablespace reaches threshold [message #495915 is a reply to message #495909] |
Wed, 23 February 2011 04:39 |
John Watson
Messages: 8964
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
