Check Datafile Usage and Resize a Datafile Oracle 10g Onward.

Due to space constraints or some other issue, we may need to resize a datafile in Oracle.
If the datafile is mostly unused (Partially used but not full).

1. Firstly, we need to check the datafile usage in each tablespace.

SQL>SELECT  a.tablespace_name, a.file_name, a.bytes allocated_bytes, b.free_bytes 

FROM dba_data_files a, (SELECT file_id, SUM(bytes) free_bytes FROM dba_free_space b GROUP BY file_id) b 

WHERE  a.file_id=b.file_id ORDER BY a.tablespace_name;

2. Then we may need to resize the datafile.

SQL>alter database datafile '/u01/app/oracle/oradata/prod_02.dbf' resize 150M;