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 -
Before proceeding, you may want to address how this was permitted to
happen.
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
> 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