Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: [Q] how to check objects on which data file?

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

From: Daniel W. Fink <optimaldba_at_yahoo.com>
Date: Mon, 21 Apr 2003 10:47:05 -0800
Message-ID: <F001.005861A0.20030421104705@fatcity.com>


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).
Received on Mon Apr 21 2003 - 13:47:05 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US