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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Cee Pee <carlospena999@xxxxxxxxx>
Sent: Wednesday, October 6, 2021 2:56 PM
To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
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.
