Re: Reclaiming space

Hello,

I always used this doc for similar activities. You can check if it helps you.

How to Resolve ORA-03297 When Resizing a Datafile by Finding the Table Highwatermark (Doc ID 130866.1)
<https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=369868253943637>

Att.

*Juliano Ribeiro*

Em qui., 7 de out. de 2021 às 12:28, Marián Bednár <marian.bednar_at_gmail.com> escreveu:

>
> I would recommend great Franck Pachot's script for freeing up all space
> above high watermark.
> But this is not a final solution, but fast for some cases...
>
>
> https://github.com/FranckPachot/scripts/blob/master/administration/resize-datafiles.sql
>
> št 7. 10. 2021 o 12:07 Douglas Dunyan <dmdunyan_at_gmail.com> napísal(a):
>
>> Greetings Carlos -
>> Before proceeding, you may want to address how this was permitted to
>> happen.
>>
>> - Consider evaluating your users, and implementing tablespace quotas
>> for users.
>> - If it may interfere with operations, why?
>> - Are users actually putting objects in application schema
>> tablespaces? If so, this should be improved.
>>
>> 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
>>
>>
>>>
>>>
>>>
>>>

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