How to Shrink the datafiles in Oracle

1. Script to generate and run to set the maxbytes of the datafiles and shrink the datafiles:
SET echo off head off serveroutput off termout off feedback off verify off space 0;
SET linesize 150;
SET pagesize 0;

COLUMN value new_val blksize;
SELECT VALUE
FROM v$parameter
WHERE NAME = 'db_block_size'
/

SPOOL gen_setmaxbytes.sql
SELECT 'spool gen_setmaxbytes.log'
FROM DUAL;
SELECT 'alter database datafile '''
|| file_name
|| ''' autoextend on next 100m maxsize '
|| BYTES / 1024 / 1024
|| 'M;'
FROM dba_data_files
WHERE maxbytes < BYTES AND maxbytes > 0
UNION ALL
SELECT 'alter database datafile '''
|| file_name
|| ''' autoextend on next 100m maxsize '
|| BYTES / 1024 / 1024
|| 'M;'
FROM dba_data_files
WHERE maxbytes = 0
UNION ALL
SELECT 'alter database datafile '''
|| file_name
|| ''' resize '
|| maxbytes / 1024 / 1024
|| 'M;'
FROM dba_data_files
WHERE maxbytes < BYTES AND maxbytes > 0
/
SELECT 'spool off;'
FROM DUAL;
SPOOL off;


SPOOL gen_shrink_dbfiles.sql
SELECT 'spool gen_shrink_dbfiles.log'
FROM DUAL;
SELECT 'alter database datafile '''
|| file_name
|| ''' resize '
|| CEIL ((NVL (hwm, 1) * &&blksize) / 1024 / 1024)
|| 'm;' cmd
FROM dba_data_files a,
(SELECT file_id, MAX (block_id + blocks - 1) hwm
FROM dba_extents
GROUP BY file_id) b
WHERE a.file_id = b.file_id(+)
AND CEIL (blocks * &&blksize / 1024 / 1024)
- CEIL ((NVL (hwm, 1) * &&blksize) / 1024 / 1024) > 0
/
SELECT 'spool off;'
FROM DUAL;
SPOOL off;


SET echo on feedback on;

@gen_setmaxbytes.sql
@gen_shrink_dbfiles.sql

EXIT;


2. Script to generate rebuild objects at the end of the given datafile:
Note: Replace the file name with actual datafile name; which you are not able to shrink after reorganization, because some objects have extents at the end of the datafile. Check and change the below script for partition tables.

SELECT mst.altst
FROM (SELECT 'ALTER TABLE ' || owner || '.' || segment_name
|| ' MOVE;' altst,
block_id bid
FROM dba_extents
WHERE file_id = (SELECT file_id
FROM dba_data_files
WHERE file_name = '/u01/oradata/ORASID/ts01.dbf')
AND segment_type = 'TABLE'
UNION
SELECT 'ALTER INDEX '
|| owner
|| '.'
|| segment_name
|| ' REBUILD;' altst,
block_id bid
FROM dba_extents
WHERE file_id = (SELECT file_id
FROM dba_data_files
WHERE file_name = '/u01/oradata/ORASID/ts01.dbf')
AND segment_type = 'INDEX'
ORDER BY bid DESC) mst;