Re: [Q] how to check objects on which data file?

An unpartitioned table/index can allocate space in a single tablespace, but in multiple datafiles. The key is that an extent can only be allocated in a single datafile. By joining dba_extents to dba_data_files, you can get a listing of all of the datafiles that contain extents belonging to a single segment or a listing by datafile of all the segments that have allocated an extent in the datafile.

Here is a script that you can use as a base script (just add conditions/formatting as desired).

select x.owner, x.segment_name, x.extent_id, x.segment_type, x.tablespace_name, f.file_name
from dba_extents x,

     dba_data_files f
where x.file_id = f.file_id
order by x.owner, x.segment_name, f.file_name

-- 
Daniel W. Fink
http://www.optimaldba.com

IOUG-A Live! April 27 - May 1, 2003 Orlando, FL
   Thursday, May 1 1:00pm - 2:00pm - Automatic Undo Internals


DENNIS WILLIAMS wrote:



>Mike

>   The hierarchy is a tablespace can hold one or more tables. A tablespace

>is comprised of one or more datafiles. You could join user_tables to

>dba_data_files, using the tablespace. But if there is more than one

>datafile, you can't be assured which datafile your table is being stored in.

>

>Dennis Williams

>DBA, 40%OCP, 100% DBA

>Lifetouch, Inc.

>dwilliams_at_lifetouch.com 

>

>

>-----Original Message-----

>Sent: Monday, April 21, 2003 11:47 AM

>To: Multiple recipients of list ORACLE-L

>

>

>We have ORACLE 8.1.7 database running.  I know I can

>select 

>"user_tables" or "user_tablespaces" to check objects

>on which 

>tablespace.  If this tablespace have several data

>files, is it possible I

>can check objects on which data files?

>

>Thanks.

>

>

>

>__________________________________________________

>Do you Yahoo!?

>The New Yahoo! Search - Faster. Easier. Bingo

>http://search.yahoo.com

>  

>



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel W. Fink
  INET: optimaldba_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).