TableSpace USR_DATA had grown to 10 GB. It has 8 datafiles, each more than
2GB.

I have dropped some of the User schemas not in use anymore. Now 3-4
datafiles are 80% free. How can I resize these datafiles to takeup less
Hard Disk Space ?

ALTER DATABASE DATAFILE <DBPATH>\USR_DATA04.dbf RESIZE 100M;
does not work.

How to list the tables/db objects that are allocated in a specific DATAFILE
?

hi,

try:

select distinct a.segment_name,b.file_name
from dba_extents a , dba_data_files b
where a.owner = ' ... ' and
a.file_id = b.file_id and
a.segment_type = 'TABLE'

marcus

--
Posted via http://dbforums.com

To help you You can use "Oracle Tool" on
http://membres.lycos.fr/pmscontact/
This tool tell you how tablespaces are used and to the tables/db
objects that are allocated in tablespaces.

This tool is free
see "administration / TableSpace's Objects" tab for these informations

regards
Philippe