Datafiles in Oracle can be resized to a smaller size only if there are no segments at the end of the file. If there is a segment at the end of the file we will not be able to reduce it size even if the rest of the file is empty.

If we knew where is the last used block in the file, we would know the minimum size of the datafile.

Use this query to get the file’s high water mark:

select /*+ rule */
a.tablespace_name,
a.file_name,
a.bytes/1024/1024 file_size_MB,
(b.maximum+c.blocks-1)*d.block_size/1024/1024 highwater
from
dba_data_files a,
(select file_id,max(block_id) maximum
from dba_extents
group by file_id) b,
dba_extents c,
    dba_tablespaces d
where a.file_id  = b.file_id
and c.file_id  = b.file_id
and c.block_id = b.maximum
  and d.tablespace_name=a.tablespace_name
order by a.tablespace_name,a.file_name;

The query returns the following columns:

TABLESPACE_NAME - the name of the tablespace

FILE_NAME - the name of the datafile

FILE_SIZE_MB - the current size of the file (in MB)

HIGHWATER - the minimum size that the file can reduced to (in MB)