Tuesday, May 11, 2010

Find datafiles' High Watermark per tablespace

This is an easy way to automatically create the commands to resize datafiles to their HWM and reclaim space.
Just, declare your tablespace name in V_TBLSPC variable:
SET SERVEROUTPUT ON

DECLARE
   V_STMT     VARCHAR2 (500);
   V_TBLSPC   VARCHAR2 (30) := 'CZD';

   CURSOR C1
   IS
      SELECT FILE_ID
        FROM DBA_DATA_FILES
       WHERE TABLESPACE_NAME = V_TBLSPC;
BEGIN
   FOR LINE IN C1
   LOOP
      SELECT    'ALTER DATABASE DATAFILE '
             || ''''
             || D.FILE_NAME
             || ''''
             || ' RESIZE '
             || NVL (CEIL (D.BYTES / 1024 / 1024 - TAKE_BACK.TAKE_BACK_MB),
                     D.BYTES / 1024 / 1024)
             || 'M;'
                SQL
        INTO V_STMT
        FROM DBA_DATA_FILES D,
             (SELECT SUM (BYTES) / 1024 / 1024 TAKE_BACK_MB
                FROM DBA_FREE_SPACE
               WHERE TABLESPACE_NAME = V_TBLSPC AND FILE_ID = LINE.FILE_ID
                     AND BLOCK_ID >=
                            NVL (
                               (SELECT (A.BLOCK_ID + (A.BYTES / B.BLOCK_SIZE))
                                  FROM DBA_EXTENTS A, DBA_TABLESPACES B
                                 WHERE A.BLOCK_ID =
                                          (SELECT MAX (BLOCK_ID)
                                             FROM DBA_EXTENTS
                                            WHERE FILE_ID = LINE.FILE_ID
                                                  AND TABLESPACE_NAME =
                                                         V_TBLSPC)
                                       AND A.FILE_ID = LINE.FILE_ID
                                       AND A.TABLESPACE_NAME = V_TBLSPC
                                       AND B.TABLESPACE_NAME = V_TBLSPC),
                               0)) TAKE_BACK
       WHERE D.FILE_ID = LINE.FILE_ID;

      DBMS_OUTPUT.PUT_LINE (V_STMT);
   END LOOP;
END;
/

A sample output of the above script is:
ALTER DATABASE DATAFILE '/oracle67/oradata/czd20.dbf' RESIZE 4994M;
ALTER DATABASE DATAFILE '/oracle7/oradata/czd05.dbf' RESIZE 3091M;
ALTER DATABASE DATAFILE '/oracle6/oradata/czd06.dbf' RESIZE 3092M;
ALTER DATABASE DATAFILE '/oracle6/oradata/czd07.dbf' RESIZE 3094M;
ALTER DATABASE DATAFILE '/oracle6/oradata/czd08.dbf' RESIZE 3093M;
ALTER DATABASE DATAFILE '/oracle6/oradata/czd09.dbf' RESIZE 3090M;
ALTER DATABASE DATAFILE '/oracle6/oradata/czd01.dbf' RESIZE 3095M;
ALTER DATABASE DATAFILE '/oracle7/oradata/czd10.dbf' RESIZE 2048M;
ALTER DATABASE DATAFILE '/oracle6/oradata/czd02.dbf' RESIZE 3096M;
ALTER DATABASE DATAFILE '/oracle7/oradata/czd03.dbf' RESIZE 3090M;
ALTER DATABASE DATAFILE '/oracle7/oradata/czd04.dbf' RESIZE 3096M;
ALTER DATABASE DATAFILE '/oracle40/oradata/czd11.dbf' RESIZE 4995M;
ALTER DATABASE DATAFILE '/oracle43/oradata/czd12.dbf' RESIZE 9998M;
ALTER DATABASE DATAFILE '/oracle48/oradata/czd13.dbf' RESIZE 8093M;
ALTER DATABASE DATAFILE '/oracle52/oradata/czd14.dbf' RESIZE 3996M;
ALTER DATABASE DATAFILE '/oracle54/oradata/czd15.dbf' RESIZE 14996M;
ALTER DATABASE DATAFILE '/oracle58/oradata/czd16.dbf' RESIZE 4000M;
ALTER DATABASE DATAFILE '/oracle59/oradata/czd17.dbf' RESIZE 9994M;
ALTER DATABASE DATAFILE '/oracle62/oradata/czd18.dbf' RESIZE 13497M;
ALTER DATABASE DATAFILE '/oracle66/oradata/czd19.dbf' RESIZE 15497M;
ALTER DATABASE DATAFILE '/oracle69/oradata/czd21.dbf' RESIZE 11993M;
ALTER DATABASE DATAFILE '/oracle71/oradata/czd22.dbf' RESIZE 9994M;
ALTER DATABASE DATAFILE '/oracle75/oradata/czd23.dbf' RESIZE 6996M;
ALTER DATABASE DATAFILE '/oracle78/oradata/czd24.dbf' RESIZE 24993M;
ALTER DATABASE DATAFILE '/oracle76/oradata/czd25.dbf' RESIZE 9994M;
ALTER DATABASE DATAFILE '/oracle78/oradata/czd26.dbf' RESIZE 3995M;
ALTER DATABASE DATAFILE '/oracle81/oradata/czd27.dbf' RESIZE 24993M;
ALTER DATABASE DATAFILE '/oracle81/oradata/czd28.dbf' RESIZE 24993M;
ALTER DATABASE DATAFILE '/oracle87/oradata/czd29.dbf' RESIZE 24985M;
ALTER DATABASE DATAFILE '/oracle88/oradata/czd30.dbf' RESIZE 24641M;
ALTER DATABASE DATAFILE '/oracle89/oradata/czd31.dbf' RESIZE 13641M;
ALTER DATABASE DATAFILE '/oracle90/oradata/czd32.dbf' RESIZE 8961M;