| Reclaim space from tablespace to file system [message #658616] |
Wed, 21 December 2016 19:55  |
 |
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: Reclaim space from tablespace to file system [message #658630 is a reply to message #658618] |
Thu, 22 December 2016 01:35  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
oratech10 wrote on Thu, 22 December 2016 03:10Yes 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: Which index to choose local or global for attached scenario [message #658642 is a reply to message #658623] |
Thu, 22 December 2016 06:42 |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
oratech10 wrote on Thu, 22 December 2016 00:41OK 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 |
cookiemonster
Messages: 13959
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).
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
