How to decrease the size of the datafiles

The dba_data_files view gives information about the physical datafiles in the database whereas dba_free_space view gives information about the free space in the datafiles with their related block id. The query below gives the datafiles free space and total space allocated for the datafiles.

select a.TABLESPACE_NAME, a.FILE_NAME, free_space/1024/1024 free_space_in_mb, a.BYTES/1024/1024 total_space_allocated_in_mb
from dba_data_files a, (select TABLESPACE_NAME,FILE_ID,sum(BYTES) FREE_SPACE from dba_free_space group by TABLESPACE_NAME,FILE_ID) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME and
a.FILE_ID=b.FILE_ID
order by a.TABLESPACE_NAME,a.FILE_NAME;

With this information one can decrease the size of the datafile by the free space with the command given below:

alter database datafile 'filepath\filename' resize filesize;

Free Space in Percentage Tablespace Wise

select z.TABLESPACE_NAME,sum(z.free_space_in_mb) free_space_in_mb,
sum(z.total_space_allocated_in_mb) total_space_allocated_in_mb,
round(sum(z.free_space_in_mb)/sum(z.total_space_allocated_in_mb)*100,2) free_pct
from (
select a.TABLESPACE_NAME, a.FILE_NAME, free_space/1024/1024 free_space_in_mb, a.BYTES/1024/1024 total_space_allocated_in_mb
from dba_data_files a, (select TABLESPACE_NAME,FILE_ID,sum(BYTES) FREE_SPACE from dba_free_space group by TABLESPACE_NAME,FILE_ID) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME and
a.FILE_ID=b.FILE_ID
) z
group by z.tablespace_name
order by z.TABLESPACE_NAME;

Free Space in Percentage At Data File Level

select a.TABLESPACE_NAME, a.FILE_NAME, free_space/1024/1024 free_space_in_mb, a.BYTES/1024/1024 total_space_allocated_in_mb, round((free_space/a.BYTES),2)*100 free_pct
from dba_data_files a, (select TABLESPACE_NAME,FILE_ID,sum(BYTES) FREE_SPACE from dba_free_space group by TABLESPACE_NAME,FILE_ID) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME and
a.FILE_ID=b.FILE_ID
order by a.TABLESPACE_NAME,a.FILE_NAME;