Alter Datafile by finding space in existing datafiles

With the help of this query we can get all available space in the datafile and upto what size it can be resized , for e.g. 

file1 is size of 10G and has 3G of free space in the datafile, this query will list you upto what the datafile can be resized , you can simply resize datafile file1 to 8G and remaining 2 G of free space u can allocate to some other datafile say file2.

set verify off

column file_name format a50 word_wrapped

column smallest format 999,990 heading "Smallest|Size|Poss."

column currsize format 999,990 heading "Current|Size"

column savings  format 999,990 heading "Poss.|Savings"

break on report

compute sum of savings on report

column value new_val blksize

select value from v$parameter where name = 'db_block_size';

/

select file_name,

       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,

       ceil( blocks*&&blksize/1024/1024) currsize,

       ceil( blocks*&&blksize/1024/1024) -

       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings

from dba_data_files a,

     ( select file_id, max(block_id+blocks-1) hwm

         from dba_extents

        group by file_id ) b

where a.file_id = b.file_id(+) order by savings desc

/