Resize all the database files
                                                 Last update (2011-03-21 12:42:22)
                                                                                                                   Date added (2010-10-20 16:38:29)

Summary
Here is a quick guide how to resize all the data files and take back some space in the database.

How to setup the automatic procedure
Create the following objects in the database
CREATE TABLE HELPDESK.FREE_SPACE
(
  TABLESPACE_NAME  VARCHAR2(30 BYTE),
  FILE_ID          NUMBER,
  BLOCK_ID         NUMBER,
  BYTES            NUMBER,
  BLOCKS           NUMBER,
  RELATIVE_FNO     NUMBER
)
TABLESPACE HELPDESK LOGGING;

INSERT INTO HELPDESK.FREE_SPACE SELECT * FROM DBA_FREE_SPACE;

COMMIT;

CREATE INDEX HELPDESK.FREE_SPACE_IDX ON HELPDESK.FREE_SPACE
(FILE_ID, BLOCK_ID) TABLESPACE HELPDESK;

CREATE TABLE HELPDESK.DBA_EXTENTS TABLESPACE HELPDESK 
AS SELECT * FROM DBA_EXTENTS;

CREATE INDEX HELPDESK.DBA_EXTENTS_IDX ON HELPDESK.DBA_EXTENTS
(FILE_ID, BLOCK_ID)
LOGGING
TABLESPACE HELPDESK
NOPARALLEL;

CREATE TABLE SYS.HWM_DATABASE
(
  FILE_ID       NUMBER,
  FILE_NAME     VARCHAR2(513 BYTE),
  SIZE_MB       NUMBER,
  TAKE_BACK_MB  NUMBER,
  RESIZE_MB     NUMBER,
  SQL           VARCHAR2(589 BYTE)
)
TABLESPACE HELPDESK;

CREATE OR REPLACE PROCEDURE Find_Datafiles_Hwm
IS
   CURSOR C IS SELECT * FROM dba_data_files;
BEGIN
   FOR c1 IN C
   LOOP
INSERT INTO HWM_database helpdesk SELECT * FROM (SELECT D.file_id, D.file_name, D.bytes/1024/1024 SIZE_MB , take_back.take_back_mb,  
D.bytes/1024/1024 - take_back.take_back_mb RESIZE_MB, 
'ALTER DATABASE DATAFILE ' || '''' || D.file_name || '''' || 
' RESIZE ' || CEIL(D.bytes/1024/1024 - take_back.take_back_mb) || 'M;' SQL
FROM dba_data_files D, (SELECT SUM (BYTES) / 1024 / 1024 take_back_mb FROM helpdesk.free_space
 WHERE tablespace_name = c1.tablespace_name 
   AND file_id = c1.file_id
   AND block_id >= NVL ((SELECT (block_id + (BYTES / 32768))
                  FROM helpdesk.dba_extents
                 WHERE block_id =
                          (SELECT MAX (block_id)
                             FROM helpdesk.dba_extents
                            WHERE file_id = c1.file_id
                              AND tablespace_name = c1.tablespace_name)
                   AND file_id = c1.file_id
                   AND tablespace_name = c1.tablespace_name),
               0
              )) take_back 
     WHERE D.file_id =c1.file_id);
--WHERE file_name LIKE '/oracle/DwhProd/oradata17%'; 
   COMMIT;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
NULL;
END Find_Datafiles_Hwm;
/
Execute the procedure Find_Datafiles_Hwm
execute Find_Datafiles_Hwm;
While the procedure is executing you can query the results from table HWM_DATABASE
SELECT * FROM HWM_DATABASE 
WHERE take_back_mb IS NOT NULL 
ORDER BY TAKE_BACK_MB DESC;

SIZE_MB TAKE_BACK_MB SQL
9520,0625 6096 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata1/DWH01/aprindata01.dbf' RESIZE 3425M;
7296,0625 4220 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata4/DWH01/scusmdata200710.dbf' RESIZE 3077M;
6912,0625 3836 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata1/DWH01/scusmdata200709.dbf' RESIZE 3077M;
67700 3563,4375 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata10/DWH01/shadow_tbs_01.dbf' RESIZE 64137M;
98184,0625 3471,75 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata8/DWH01/dsa_tab04.dbf' RESIZE 94713M;
10720,0625 3376 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata4/DWH01/acuacindx.dbf' RESIZE 7345M;
60000 1400,8125 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata5/DWH01/ods_tab01.dbf' RESIZE 58600M;
7700 1165 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata10/DWH01/fast_1024_10.dbf' RESIZE 6535M;
6300 1040 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata5/DWH01/fast_1024_05.dbf' RESIZE 5260M;
6400 1035 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata9/DWH01/fast_1024_09.dbf' RESIZE 5365M;
6300 1030 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata4/DWH01/fast_1024_04.dbf' RESIZE 5270M;
6300 1025 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata7/DWH01/fast_1024_07.dbf' RESIZE 5275M;
6300 1025 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata8/DWH01/fast_1024_08.dbf' RESIZE 5275M;
2176 1024 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata4/DWH01/fbidedata200709d11to20.dbf' RESIZE 1152M;
2176 1024 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata9/DWH01/fbilldata200709d01to10.dbf' RESIZE 1152M;
2176 1024 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata5/DWH01/fbilldata200709d11to20.dbf' RESIZE 1152M;
2176 1024 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata2/DWH01/fbilldata200709d21to31.dbf' RESIZE 1152M;
3200 1024 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata2/DWH01/fbidedata200710d01to10.dbf' RESIZE 2176M;
2176 1024 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata1/DWH01/fbidedata200710d21to31.dbf' RESIZE 1152M;
2176 1024 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata9/DWH01/fbilldata200710d01to10.dbf' RESIZE 1152M;
2176 1024 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata5/DWH01/fbilldata200710d11to20.dbf' RESIZE 1152M;
2176 1024 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata2/DWH01/fbilldata200710d21to31.dbf' RESIZE 1152M;
3200 1024 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata2/DWH01/fbidedata200709d01to10.dbf' RESIZE 2176M;
2176 1024 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata2/DWH01/fbilldata200708d21to31.dbf' RESIZE 1152M;
2176 1024 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata5/DWH01/fbilldata200708d11to20.dbf' RESIZE 1152M;
2176 1024 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata9/DWH01/fbilldata200708d01to10.dbf' RESIZE 1152M;
2176 1024 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata1/DWH01/fbidedata200708d21to31.dbf' RESIZE 1152M;
3200 1024 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata2/DWH01/fbidedata200708d01to10.dbf' RESIZE 2176M;
6600 1024 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata11/DWH01/fast_1024_11.dbf' RESIZE 5576M;
3000 1008 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata5/DWH01/ffichdata200709d01to10.dbf' RESIZE 1992M;
4448,0625 928 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata4/DWH01/aprindata02.dbf' RESIZE 3521M;
3000 896 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata2/DWH01/fbidedata200712d01to10.dbf' RESIZE 2104M;
6200 883 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata3/DWH01/fast_1024_03.dbf' RESIZE 5317M;
6200 842 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata17/DWH01/fast_1024_17.dbf' RESIZE 5358M;
6200 841 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata2/DWH01/fast_1024_02.dbf' RESIZE 5359M;
6300 840 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata16/DWH01/fast_1024_16.dbf' RESIZE 5460M;
6200 822 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata1/DWH01/fast_1024_01.dbf' RESIZE 5378M;
6300 822 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata15/DWH01/fast_1024_15.dbf' RESIZE 5478M;
38076 800,8125 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata2/DWH01/star_idx01.dbf' RESIZE 37276M;
4096,0625 768 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata3/DWH01/fdiindata01.dbf' RESIZE 3329M;
4096,0625 576 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata4/DWH01/fdiindata02.dbf' RESIZE 3521M;
2000 473,875 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata2/DWH01/owf01.dbf' RESIZE 1527M;
1024 464 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata6/DWH01/gusbidata200712.dbf' RESIZE 560M;
6144 376 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata5/DWH01/scusmdata200708.dbf' RESIZE 5768M;
2432 256 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata1/DWH01/fbidedata200709d21to31.dbf' RESIZE 2176M;
512,0625 243,59375 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata2/DWH01/ddata.dbf' RESIZE 269M;
113784 195,6875 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata3/DWH01/scratch01.dbf' RESIZE 113589M;
11190 190,875 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata2/DWH01/sysaux01.dbf' RESIZE 11000M;
60560 181,4375 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata5/DWH01/dsa_tab03.dbf' RESIZE 60379M;
512 166 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata5/DWH01/guspddata200804.dbf' RESIZE 346M;
512 158 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata4/DWH01/guspddata200803.dbf' RESIZE 354M;
512 142 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata6/DWH01/guspddata200805.dbf' RESIZE 370M;
80680 140,75 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata9/DWH01/dsa_tab05.dbf' RESIZE 80540M;
512 138 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata2/DWH01/guspddata200801.dbf' RESIZE 374M;
200 136 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata1/DWH01/guspddata200712.dbf' RESIZE 64M;
512 132 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata7/DWH01/guspddata200806.dbf' RESIZE 380M;
3600 90,75 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata12/DWH01/fast_16_12.dbf' RESIZE 3510M;
9232,0625 88 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata3/DWH01/dclidata.dbf' RESIZE 9145M;
30278 84,875 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata2/DWH01/dsa_idx02.dbf' RESIZE 30194M;
7700 81,875 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata6/DWH01/geneva_data03.dbf' RESIZE 7619M;
1024 80 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata7/DWH01/gusbidata200801.dbf' RESIZE 944M;
6912,0625 64 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata2/DWH01/sprimdata200709.dbf' RESIZE 6849M;
6144 64 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata3/DWH01/sprimdata200708.dbf' RESIZE 6080M;
200 64 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata5/DWH01/fbilldata200808d11to20.dbf' RESIZE 136M;
328 64 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata4/DWH01/fbilldata200808d01to10.dbf' RESIZE 264M;
1792 64 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata8/DWH01/gusbidata200802.dbf' RESIZE 1728M;
972 36 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata7/DWH01/ffichdata200712d21to30.dbf' RESIZE 936M;
58800 32,8125 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata9/DWH01/ods_tab03.dbf' RESIZE 58768M;
6528 32 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata3/DWH01/fpaymdata200708.dbf' RESIZE 6496M;
908 32 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata6/DWH01/ffichdata200708d11to20.dbf' RESIZE 876M;
6400,0625 32 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata3/DWH01/sprimdata200710.dbf' RESIZE 6369M;
70620 28,375 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata8/DWH01/dsa_tab01.dbf' RESIZE 70592M;
4992,0625 24 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata2/DWH01/dcustindx.dbf' RESIZE 4969M;
228,0625 20 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata3/DWH01/apex01.dbf' RESIZE 209M;
1036 20 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata7/DWH01/ffichdata200708d21to30.dbf' RESIZE 1016M;
940 16 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata7/DWH01/ffichdata200709d21to30.dbf' RESIZE 924M;
11600,0625 16 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata2/DWH01/dbiacdata.dbf' RESIZE 11585M;
940 16 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata6/DWH01/ffichdata200709d11to20.dbf' RESIZE 924M;
15360 15,875 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata9/DWH01/users201.dbf' RESIZE 15345M;
10 9,84375 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata8/DWH01/guscddata200710.dbf' RESIZE 1M;
10 9,84375 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata7/DWH01/guscddata200709.dbf' RESIZE 1M;
4016,0625 8 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata3/DWH01/dbiacindx.dbf' RESIZE 4009M;
10 8 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata5/DWH01/glrpmdata200710.dbf' RESIZE 2M;
10 8 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata4/DWH01/glrpmdata200709.dbf' RESIZE 2M;
10 8 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata3/DWH01/glrcmdata200710.dbf' RESIZE 2M;
10 8 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata2/DWH01/glrcmdata200709.dbf' RESIZE 2M;
10 8 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata1/DWH01/glrcmdata200708.dbf' RESIZE 2M;
10 8 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata3/DWH01/glrpmdata200708.dbf' RESIZE 2M;
6400 5 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata14/DWH01/fast_1024_14.dbf' RESIZE 6395M;
10200 4,875 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata5/DWH01/ods_idx01.dbf' RESIZE 10196M;
3370 3,0625 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata10/DWH01/monitor_tbs_01.dbf' RESIZE 3367M;
10,0625 0,9375 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata3/DWH01/apex_files01.dbf' RESIZE 10M;
49620,0625 0,875 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata3/DWH01/dsa_idx01.dbf' RESIZE 49620M;
52568,0625 0,875 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata1/DWH01/ods_tab02.dbf' RESIZE 52568M;
28280 0,875 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata4/DWH01/owb01.dbf' RESIZE 28280M;
37158 0,8125 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata1/DWH01/dsa_idx03.dbf' RESIZE 37158M;
69620 0,75 ALTER DATABASE DATAFILE '/oracle/DwhProd/oradata1/DWH01/snap01.dbf' RESIZE 69620M;
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search