Re: Reclaiming space
Date: Thu, 7 Oct 2021 03:05:33 -0700
Message-ID: <CA+UckTsTYemN40RJxXJ7ryk6pc=Jqb3L3B_v-xVLjv8xJ_GcyQ_at_mail.gmail.com>
Greetings Carlos -
For your remediation plan, it will be time consuming. That's the nature
of the beast. In Oracle, there are usually several roads to get to the
same destination. In the old days, a tablespace space reorg meant export
tablespace, drop and recreate tablespace, then import. While that is still
an option, today, we have DBMS_REDEFINITION which could be your friend
here.
By nature of your comment, "the space was not reclaimed in the OS" we can
assume this is not a BIGFILE tablespace, as OS limitations of file size is
likely 32Gb. 2Tb of 32Gb datafiles would indicate at least 64 datafiles.
If this tablespace is 'several terabytes' you have a long road to hoe.
Even before developing a plan, I would likely shoot from the hip, and
observe the results. For instance, I would prepare a script that would
"alter....datafile resize 1G" for every datafile in the tablespace, and
execute it. The risk is minimal, as if there are segments beyond the 1Gb
mark, Oracle will simply return an error. Next I would re-run the script
with a change of 1G to 100Mb. That will enable you to reclaim as much as
possible, as quickly as possible.
Depending on the results, I would then evaluate to determine if more
effort is warranted. Mark's response has great validity. What is the
expected growth rate in the next 6 months?
If additional effort is required, you'll need to evaluate all the
datafiles, to determine which datafiles have segments in use, the high
water marks for each, etc. Essentially, you'll be looking at the segment
map for each datafile. From that information, you can develop your plan
for further remediation.
I hope this makes sense to you. Remember, as an Oracle DBA, If it were
easy, everyone would do it ! :-)
Doug
On Wed, Oct 6, 2021 at 12:31 PM Powell, Mark <mark.powell2_at_dxc.com> wrote:
> Cee, first is reclaiming the space worth the effort based on how much
Before proceeding, you may want to address how this was permitted to
happen.
> 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-lReceived on Thu Oct 07 2021 - 12:05:33 CEST