| Recover space after Index rebuild [message #504615] |
Tue, 26 April 2011 15:34  |
preet_kumar
Messages: 204
Registered: March 2007
|
Senior Member |
|
|
We have separate tablespaces for Tables and Indexes.
Also Temp is in different temporary tablespace and UNDO also in UNDO tablespace and Index tablespace contains only Indexes.
The tablespace usage for tables is 80% and Index is 91%.There is not enough disk space to allocate for the datafile on our system.
I ran a rebuild on one of the Index but now notice the Index tablespace is 98% used soon after the rebuild finished.
How can i free up space for Index tablespace and why does the size of Index tablespace increased after the rebuild.
Thanks
[Updated on: Tue, 26 April 2011 15:40] Report message to a moderator |
|
|
|
|
|
| Re: Recover space after Index rebuild [message #504621 is a reply to message #504616] |
Tue, 26 April 2011 16:06  |
preet_kumar
Messages: 204
Registered: March 2007
|
Senior Member |
|
|
May i know what is wrong with my post and why you think i did not follow the posting guidelines ?
Yes eliminating Indexes and tables would free up lots of space but my question is why after rebuild i see increase in tablespace usage of 7%
|
|
|
|
|
|
| Re: Recover space after Index rebuild [message #504652 is a reply to message #504641] |
Wed, 27 April 2011 01:52 |
preet_kumar
Messages: 204
Registered: March 2007
|
Senior Member |
|
|
Thanks a lot Michel for your response.
So the increase in space after rebuild is also due to the pctfree of the new segement.
Coalesce and Shrink does not use a new segment does that mean it will help me to get some more space.
|
|
|
|
| Re: Recover space after Index rebuild [message #504656 is a reply to message #504652] |
Wed, 27 April 2011 02:30 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:So the increase in space after rebuild is also due to the pctfree of the new segement.
Likely.
Quote:Coalesce and Shrink does not use a new segment does that mean it will help me to get some more space.
It depends sometimes more, sometimes less.
Regards
Michel
[Updated on: Wed, 27 April 2011 02:30] Report message to a moderator |
|
|
|
| Re: Recover space after Index rebuild [message #504717 is a reply to message #504615] |
Wed, 27 April 2011 08:12 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
preet_kumar wrote on Tue, 26 April 2011 16:34There is not enough disk space to allocate for the datafile on our system.
I really am not trying to sound harsh here, but this is really shortsightedness if you are that low on disk space. Is this a static database that no data is ever added to? If not, then your data will eventually overrun your tablespace and then what?
|
|
|
|
| Re: Recover space after Index rebuild [message #504897 is a reply to message #504717] |
Thu, 28 April 2011 07:07 |
preet_kumar
Messages: 204
Registered: March 2007
|
Senior Member |
|
|
Anyway this is not a production system and i also don't need to look for solution here if i want to add more memory or disk space.
If 3 million records are deleted from the table and the Index is rebuild and if this causes the tablespace to increase 8% then i am looking for a solution by which i can reclaim the space not keep on adding datafiles.
|
|
|
|
| Re: Recover space after Index rebuild [message #504902 is a reply to message #504897] |
Thu, 28 April 2011 07:12 |
|
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Drop and recreate the index.
But if your tablespace is in ASSM then Oracle will spread the index extents/blocks accross the tablespace anyway and will surely use the blocks at the end of it prevneting you from reclaiming space.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
