All too often we attempt to resize a datafile similar to the following:
alter database datafile '/oradata/orcl/users01.dbf' resize 1G;
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
Oops, the datafile contains some pesky segment past the 1GB desired size so the resize operation fails. We should be able to shrink a datafile, and have Oracle relocate segments to earlier in the datafile, and rebuild indexes for any tables that have moved. A command similar to the following:
ALTER DATABASE DATAFILE '/oradata/orcl/users01.dbf' SHRINK 1G [ONLINE] [REBUILD INDEXES];
The SHRINK command causes segments to be moved, different than the current RESIZE command. The optional ONLINE clause would let this be performed online, and the optional REBUILD INDEXES clause would rebuild the indexes after any segments have been moved.
Obviously this would have the potential for negative performance if done online with high rates of concurrent I/O on the datafile, but that's up to the DBA to understand the implications.