Here is a quick guide how to resize all the data files and take back some space in the database.
While the procedure is executing you can query the results from table HWM_DATABASE
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; |