Reclaiming free space in datafiles

See the amount of free space in each datafile:

select df.file_name, round(max(df.bytes) / 1048576) file_mb, round(sum(dfs.bytes) / 1048576) file_free_mb
  from dba_data_files df
     , dba_free_space dfs
 where df.tablespace_name = '<tablespace_name>'
   and dfs.file_id = df.file_id
 group by df.file_name;

What is the last block and how much can we shrink a datafile?

select file_name, owner, segment_name, partition_name, segment_type, round(file_bytes / 1048576) data_file_mb, round((block_id + blocks) * block_size / 1048576) object_end_mb
     , 'alter database datafile ''' || file_name || ''' resize ' || round((block_id + blocks) * block_size / 1048576) || 'M;'
  from (
select df.file_name file_name
     , df.bytes file_bytes
     , rank() over (partition by df.file_name order by block_id desc) rang
     , de.owner
     , de.segment_name
     , de.partition_name
     , de.segment_type
     , de.block_id
     , de.blocks
     , ts.block_size
  from dba_data_files df
     , dba_extents de
     , dba_tablespaces ts
 where df.tablespace_name = '<tablespace_name>'
   and de.file_id = df.file_id
   and ts.tablespace_name = df.tablespace_name)
where rang < 2;

Use rang < 11 to see the 10 last segments for each datafile.

Rebuild statements:

alter table TABLE_NAME move;
alter index INDEX_NAME rebuild online;
alter index INDEX_NAME rebuild partition PARTITION online;