How to reduce the size of Data file in Oracle

 

file_downsize.sql

set lines 300 pages 300 ;

col file_name for a70 ;

col "Tablespace NAME" format a30

select b.file_id ,b.file_name ,a.tablespace_name "Tablespace_NAME", round((b.totalspace - a.freespace),1)"USED_SPACE_GB",

round(a.freespace,1) "FREE_SPACE_GB",round(b.totalspace) "TOTAL_SPACE_GB"

from

(select file_id ,tablespace_name,file_name,(bytes)/1024/1024/1024 TotalSpace

from dba_data_files) b,

(select file_id,tablespace_name,sum(bytes)/1024/1024/1024 FreeSpace

from dba_free_space

group by tablespace_name,file_id ) a

where b.tablespace_name = a.tablespace_name(+)

and a.file_id=b.file_id

and b.tablespace_name like upper('%&tablespace_name%')

order by 3,1;