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