Re: Reclaiming space

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