dba_extents columns

OWNER           VARCHAR2 Owner of the segment associated with the extent
SEGMENT_NAME    VARCHAR2 Name of the segment associated with the extent
PARTITION_NAME  VARCHAR2 Partition/Subpartition Name of the segment
SEGMENT_TYPE    VARCHAR2 Type of the segment
TABLESPACE_NAME VARCHAR2 Name of the tablespace containing the extent
EXTENT_ID       NUMBER   Extent number in the segment
FILE_ID         NUMBER   Name of the file containing the extent
BLOCK_ID        NUMBER   Starting block number of the extent
BYTES           NUMBER   Size of the extent in bytes
BLOCKS          NUMBER   Size of the extent in ORACLE blocks
RELATIVE_FNO    NUMBER   Relative number of the file containing the segment                  header

dba_extents scripts

Here are some scripts that use dba_extents to examine the number of extents used by an object:

Amount of spaced used from dba_extents:

Select
   tablespace_name,sum(bytes) bytes_full
From
   dba_extents
Group by
   tablespace_name;

Total available space from dba_data_files:

Select
   tablespace_name,
   sum(bytes) bytes_total
From
   dba_data_files
Group by
   tablespace_name;

So if we add this with our original statement, we can select on pct_used (less than 70 percent):

select 'alter database '||a.name||' datafile '''||b.file_name||''''
from v$database a
,dba_data_files b
,(Select tablespace_name,sum(bytes) bytes_full
From dba_extents
Group by tablespace_name) c
,(Select tablespace_name,sum(bytes) bytes_total
From dba_data_files
Group by tablespace_name) d
Where b.tablespace_name = c.tablespace_name
And b.tablespace_name = d.tablespace_name
And bytes_full/bytes_total < .7
;

According to the command, a selection has been made based on tablespace. What if you want to resize based on file? It's crucial to remember that multiple files can exist in any tablespace. Plus, only space that is after the last data block can be de-allocated. So the next step should be to find the last data block:

select
   tablespace_name,
   file_id,max(block_id) max_data_block_id
from
   dba_extents
group by
   tablespace_name,file_id;

Now that the command to find the last data block has been inserted, it is time to find the free space in each file above that last data block:

Select
   a.tablespace_name,
   a.file_id,
   b.bytes    bytes_free
From
   (select
      tablespace_name,
      file_id,max(block_id) max_data_block_id
    from
      dba_extents
    group by tablespace_name,file_id) a,
   dba_free_space b
where
   a.tablespace_name = b.tablespace_name
and
   a.file_id = b.file_id
and
   b.block_id > a.max_data_block_id;

See

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.