RE: Reclaiming space

Of course the simplest sledge hammer is "move once" emptying out the offending tablespace and eventually dropping it.  

For ongoing long term strategy it is useful in many environments to identify the objects that tend to transiently require massive storage per row
(usually some sort of lob) and/or which tend to migrate and chain but which
eventually stabilize for an individual row. The crux to handling this without a treadmill operation is to have a pretty good (good enough to not care) way to identify when a row has stabilized. Often that is no more than a status code or a rule based on some datetime column (such as these transactions may not be "corrected" after 6 months; if a correction is require after that a correcting transaction is required).  

Then, in an ebb of activity in your processing schedule, you sweep through placing the identified quiescent rows in the designated time based or forever archive tablespace for the relevant object.  

You can monitor for unfreeable space in the "active" tablespaces, but as it has already been pointed out, it is often the case that you'll need that space on a cyclic basis. IF occasional actual shrinks are operationally convenient (or required such as having some very active tablespaces completely on duplexed RAM and you're running out and can't get more) you arrange a time in your schedule to copy the current contents and drop the air laden original (possibly recreating it empty and moving the contents back if the original storage was "special.")  

Once upon a time the difference in speed of the fastest and slowest storage was orders of magnitude and the fastest storage was both extraordinarily expense and limited in available size, so engineering this sort of thing optimally paid the rent.  

Good luck, try to avoid operational treadmills, and don't fall victim to Compulsive Tuning Disorder (CTD). (CTD was coined by Gaja Krishna Vaidyanatha, who I am happy to call an old friend.)  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Powell, Mark
Sent: Wednesday, October 06, 2021 3:32 PM To: Oracle-L Freelists
Subject: Re: Reclaiming space  

Cee, first is reclaiming the space worth the effort based on how much space will be taken by new extents from the objects in the tablespace over the next six months to year?  

If you think it is worth re-claiming then if you are on version 12.2 you can look at online heap table moves and partition moves to move objects from the logical end of the file(s) in the tablespace to the logical beginn. Then you can shrink the file(s) to release space at the logical file end.  

Mark Powell

Database Administration

(313) 592-5148
   

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Cee Pee <carlospena999_at_gmail.com> Sent: Wednesday, October 6, 2021 2:56 PM To: Oracle-L Freelists <oracle-l_at_freelists.org> Subject: Reclaiming space  

Hi,  

I have been working for a long time and I still did not know this. We had a user whose objects were occupying about 2Tb. We dropped the user cascade in the DB to reclaim space on the OS side. I was hoping the user's objects would be dropped and also the space reclaimed. But the space was not reclaimed in the OS. We dont see any object owned by the user in the dba_segments.  

More reading up revealed that the space is not released to the OS but is available for future storage for other objects inside the tablespace. If we want to reclaim the space, one option seems to be to reorganize the tablespace. Re-organizing for us may be time consuming and interfere with operations; the whole tablespace is several TB in size. Is there a better way? v12  

CP.  

--
http://www.freelists.org/webpage/oracle-l