Reclaim disk space from database/ Reduce data file size to free disk

My tablespace is fragmented and I want to reduce the size of my dbf files to free up disk. How do I reclaim disk space in Oracle? 
 
Oracle maps the physical files (customer.dbf) to their logical "tablespace" construct and Oracle places objects (tables & indexes) into the tablespace.
Within the tablespace, objects are scattered throughout the tablespace and corresponding datafiles.

Tables, indexes and tablespaces will naturally fragment as a function of update activity and Oracle has many methods for reclaiming disk space and a segment advisor which will recommend when tables and indexes will benefit from a reorganization to free up disk space.

Segments that undergo significant data manipulation language (DML) activity, such as UPDATE and DELETE operations, can become sparsely populated, with chunks of free space within their data blocks. Besides simply wasting space, sparsely populated segments can also lead to poor performance, because operations such as a full table scan will need to scan more blocks than necessary to retrieve data.


Before Oracle Database 10g, you could reclaim the free segment space by dropping the table, re-creating it, and then reloading the data. You could also use the ALTER TABLE MOVE command to move the table to a different tablespace. Both of these processes, however, must occur with the table offline. Online table reorganization is another way to reclaim space, but it requires significant disk space.

So how much DISK space you can claim from your data file (Calculate)?

Let run this script:

SELECT substr(FILE_NAME,1,55),
CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
FROM DBA_DATA_FILES DBADF, ( SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS
WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) Order by SAVINGS

You will ask for BOCKSIZE.

You can find database block size from intXXX.ora file.
db_block_size=8192

So in this case enter 8192 as parameter.

The output will be like this:

ORCL: SQL> SELECT substr(FILE_NAME,1,55),
2 CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
3 CEIL( BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
4 CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
5 FROM DBA_DATA_FILES DBADF, ( SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS
6 WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) Order by SAVINGS
7 /
old 2: CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
new 2: CEIL( (NVL(HWM,1)*8192)/1024/1024 ) SMALLEST,
old 3: CEIL( BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
new 3: CEIL( BLOCKS*8192/1024/1024) CURRSIZE,
old 4: CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
new 4: CEIL( BLOCKS*8192/1024/1024) - CEIL( (NVL(HWM,1)*8192)/1024/1024 ) SAVINGS

SUBSTR(FILE_NAME,1,55) SMALLEST CURRSIZE SAVINGS
------------------------------------------------------- ---------- ---------- ----------
D:\ ORADATA\ORCL\DATA_AUD_02.DBF 80 80 0
D:\ ORADATA\ORCL\DATA6_02.DBF 200 200 0
D:\ ORADATA\ORCL\DATA1_02.DBF 520 520 0
D:\ ORADATA\ORCL\DATA_S_03.DBF 20 20 0
D:\ ORADATA\ORCL\DATA_AUD_01.DBF 60 60 0
D:\ ORADATA\ORCL\DATA_02.DBF 120 120 0
D:\ ORADATA\ORCL\DATA4_01.DBF 20 20 0
D:\ ORADATA\ORCL\DATA5_02.DBF 100 100 0
D:\ ORADATA\ORCL\DATA_AUDI_01.DBF 17 20 3
D:\ ORADATA\ORCL\SYSAUX01.DBF 197 200 3
D:\ ORADATA\ORCL\DATA_AUDI_02.DBF 16 20 4

SUBSTR(FILE_NAME,1,55) SMALLEST CURRSIZE
------------------------------------------------------- ---------- ---------- ----------
D:\ ORADATA\ORCL\DATA_01.DBF 135 140 5
D:\ ORADATA\ORCL\DATA_SI_01.DBF 13 20 7
D:\ ORADATA\ORCL\DATA6_01.DBF 213 220 7
D:\ ORADATA\ORCL\DATA2_02.DBF 9 20 11
D:\ ORADATA\ORCL\DATA2_01.DBF 8 20 12
D:\ ORADATA\ORCL\DATA4_02.DBF 27 40 13
D:\ ORADATA\ORCL\DATA_S_02.DBF 26 40 14
D:\ ORADATA\ORCL\DATA1_01.DBF 545 560 15
D:\ ORADATA\ORCL\USERS01.DBF 2 19 17
D:\ ORADATA\ORCL\DATA5_01.DBF 102 120 18
D:\ ORADATA\ORCL\DATA_HL7_02.DBF 1 20 19

SUBSTR(FILE_NAME,1,55) SMALLEST CURRSIZE SAVINGS
------------------------------------------------------- ---------- ---------- ----------
D:\ ORADATA\ORCL\DATA_HL7I_02.DBF 1 20 19
D:\ ORADATA\ORCL\DATA3_01.DBF 1 20 19
D:\ ORADATA\ORCL\DATA_HL7_01.DBF 1 20 19
D:\ ORADATA\ORCL\DATA_HL7I_01.DBF 1 20 19
D:\ ORADATA\ORCL\DATA3_02.DBF 1 20 19
D:\ ORADATA\ORCL\DATA_S_01.DBF 20 40 20
D:\ ORADATA\ORCL\DATA_SI_02.DBF 16 40 24

SUBSTR(FILE_NAME,1,55) SMALLEST CURRSIZE SAVINGS
------------------------------------------------------- ---------- ---------- ----------
D:\ ORADATA\ORCL\SYSTEM01.DBF 265 300 35
D:\ ORADATA\ORCL\UNDOTBS01.DBF 34 85 51

Let’s write a dynamic script to resize data file


set pagesize 0
set verify off
set heading off
set feedback off
set linesize 300

prompt Running Shrink_data_&&1..dbsql
spool Shrink_data_&&1..dbsql

SELECT 'ALTER DATABASE DATAFILE '|| ' '''|| substr(FILE_NAME,1,65) ||' '''|| ' RESIZE ' || CEIL(
(NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) ||'M; ' FROM DBA_DATA_FILES DBADF, ( SELECT FILE_ID,MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) and CEIL(
BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 )>1

spool off
set echo on
spool Shrink_data_&&1..log

rem @Shrink_data_&&1..dbsql
spool

spool off

exit

You will see all entries of data files in spool output (Shrink_data.log).

For example:
ALTER DATABASE DATAFILE 'D:\ ORADATA\ORCL\SYSTEM01.DBF ' RESIZE 265M;

This is a dynamic script and tested in several databases.