Home » RDBMS Server » Server Administration » Reclaim space from tablespace to file system
Reclaim space from tablespace to file system [message #658616] Wed, 21 December 2016 19:55 Go to next message
oratech10
Messages: 26
Registered: September 2011
Junior Member
ofcourse DELETE is an option..however for 10.2.0.3 release can we get storage space after delete? I thought if we delete performance may slow down of that table, also we may not get the storage space.
Re: Which index to choose local or global for attached scenario [message #658617 is a reply to message #658616] Wed, 21 December 2016 20:34 Go to previous messageGo to next message
BlackSwan
Messages: 26194
Registered: January 2009
Location: SoCal
Senior Member
After you DELETE rows, then Oracle will add new rows into the space the old rows existed.

Oracle is capable & designed to do its own housekeeping & reuse available disk space.

So why do you feel compelled to "reclaim" space?
Re: Which index to choose local or global for attached scenario [message #658618 is a reply to message #658617] Wed, 21 December 2016 21:10 Go to previous messageGo to next message
oratech10
Messages: 26
Registered: September 2011
Junior Member
Yes I want to reclaim space. Is it possible? because I have many such big tables where I want to remove older data (like data older than 2 years) and want to gain space at file system level.
Re: Which index to choose local or global for attached scenario [message #658619 is a reply to message #658616] Wed, 21 December 2016 22:40 Go to previous messageGo to next message
BlackSwan
Messages: 26194
Registered: January 2009
Location: SoCal
Senior Member
move the table into different tablespace
Re: Which index to choose local or global for attached scenario [message #658623 is a reply to message #658616] Thu, 22 December 2016 00:41 Go to previous messageGo to next message
oratech10
Messages: 26
Registered: September 2011
Junior Member
OK noted. thanks Swan. Let me know if any other possible methods to gain space at file system level. thanks
Re: Which index to choose local or global for attached scenario [message #658629 is a reply to message #658623] Thu, 22 December 2016 01:26 Go to previous messageGo to next message
John Watson
Messages: 7618
Registered: January 2010
Location: Global Village
Senior Member
THis is nothing to do with partitioning. I'll split it into a separate topic, please do so yourself in future.
Re: Reclaim space from tablespace to file system [message #658630 is a reply to message #658618] Thu, 22 December 2016 01:35 Go to previous messageGo to next message
John Watson
Messages: 7618
Registered: January 2010
Location: Global Village
Senior Member
oratech10 wrote on Thu, 22 December 2016 03:10
Yes I want to reclaim space. Is it possible? because I have many such big tables where I want to remove older data (like data older than 2 years) and want to gain space at file system level.
This is really awkward in an Oracle database. BS's advice is probably the only sure way. Otherwise, you can try ALTER TABLE...SHRINK SPACE then ALTER DATABASE DATAFILE...RESIZE...
Re: Reclaim space from tablespace to file system [message #658632 is a reply to message #658630] Thu, 22 December 2016 03:46 Go to previous messageGo to next message
oratech10
Messages: 26
Registered: September 2011
Junior Member
Ok noted. thanks. We can close this case. Thanks a lot for all your valuable inputs. Cheers!!
Re: Which index to choose local or global for attached scenario [message #658642 is a reply to message #658623] Thu, 22 December 2016 06:42 Go to previous messageGo to next message
EdStevens
Messages: 981
Registered: September 2013
Senior Member
oratech10 wrote on Thu, 22 December 2016 00:41
OK noted. thanks Swan. Let me know if any other possible methods to gain space at file system level. thanks
In most cases, this is a fool's errand.
If you delete rows from a table, the extents that had been allocated will remain allocated to that table and will be re-used as new rows are added. You say you are going to delete a lot of old data, but won't you also be continually adding new data? If you were to do some sort of re-orgnization of the table to release those currently un-used extents, then as soon you you start inserting new rows, oracle with have to go to all the trouble to get new extents. Extent acquisition and allocation is a fairly expensive operation for the database.

And if you do re-organize the table and release those extents, they are simply de-allocated within the tablespace - to be available for re-allocation when the time comes.

To release the space back to the OS file system, you have to follow deleting the rows with re-organizing the table to de-allocate the extents, and then follow that with shrinking the data file, which may not be all that easy. Regardless of how much unused space is in the data file, a resize operation can only shrink it down to the high water mark. And unless the table you shrink is the one that is holding the extent at the HWM, you still won't be able to reclaim any space.

So you have to ask yourself
1) How much space do I stand to regain to the OS file system?
2) Is that space really significant? Really? REALLY?
3) Is it really worth the trouble to regain that space?
Re: Which index to choose local or global for attached scenario [message #658656 is a reply to message #658642] Thu, 22 December 2016 10:33 Go to previous messageGo to next message
cookiemonster
Messages: 13285
Registered: September 2008
Location: Rainy Manchester
Senior Member
Inversely the only time it really makes sense to do this is you somehow ended up with a lot more data in the DB than you normally expect to hold and you've now got rid of that excess.
In that case, and you really need to reclaim space for some other purpose then it's probably worth it.
If that's not the case then as Ed says it's almost certainly a fools errand, oracle will take the space back anyway (or if you've stopped it from growing, run out of space).
Re: Which index to choose local or global for attached scenario [message #658671 is a reply to message #658656] Thu, 22 December 2016 19:57 Go to previous message
oratech10
Messages: 26
Registered: September 2011
Junior Member
Yes understood.. thanks a lot for detail and clear explanation Smile
Previous Topic: Is there a optimal size for file next extent size?
Next Topic: help: i wanno get an oracle11g dsi
Goto Forum:
  


Current Time: Thu Oct 18 21:14:16 CDT 2018