Re: Reclaiming space

Thank you Marian and everyone else who responded. Franck's script did it for us. It released over 1.5TB of space - that really helped.

I am aware that it can grow back, but we were in a crunch mode the other day.

On Thu, Oct 7, 2021 at 5:28 AM Marián Bednár <marian.bednar_at_gmail.com> wrote:

>
> 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