Re: How to tell which datafile a table is in.

Graham,

A word of warning, with this solution. From sys.tab$ you get the file# which the segment header is in, but if the tablespace has multiple data files, you wont get all the data files effected. For this, you need to join to sys.uet$, based upon the obj# of tab$.

i.e.
select uet.file# from uset$ uet, tab$ tab

where tab.obj# = &obj
  and uet.segfile# = tab.file#
  and uet.segblock# = tab.block#

Alternatively, and this is a little slower, but easier, you can query DBA_EXTENT as follows;

select file_id
from dba_extents
where segment_name = '&SEGMENT_NAME'

Once you have the list of file#'s you continue as before.

Rgds
Andrew Babb

Graham C Thornton wrote:

> Hi.
>
> My news-server had already purged most of this thread by the time I found
> it,
> so this answer may have been given before, I apologise for any
> repitition....
>
> To find out which datafile a table named MYTABLE is in....
>
> 1. Get the object no. of the table:
>
> SQL> select obj# from sys.obj$ where name = 'MYTABLE';
>
> OBJ#
> ----------
> 4538
>
> 2. Get the file no the object is in:
>
> SQL> select file# from sys.tab$ where obj# = 4538;
>
> FILE#
> ----------
> 8
>
> 3. Get the filename of the file no.
>
> SQL> select name from sys.v_$datafile where file# = 8;
>
> NAME
> ----------------------------------------------------------------------------
> ----
> DSA2:[ORACLE7.DB_ORADB7]USER_TBS_01.DBS
>
> SQL>
>
> You need to have DBA privilege for this to work.
>
> Regards
>
> Graham
>
> Dr. Jan Dieckmann wrote in message <36DD4D6F.48287345_at_psi.de>...
> >You can use the tabregister "tablespace" of the tool Hora 3. See
> >http://www.keeptool.com for a free trial version of Hora 3.
> >
> >regards Jan Dieckmann
> >
> >tim.mcconechy_at_runtime.dk wrote:
> >
> >> How can I tell which datafile a table is created into...
> >>
> >> Can i control this.
> >> I hvae windows NT
> >> but would like to know the answer for UNIX too.
> >>
> >> I thought it was v$sqlare but can't seem to extract the datafile name...
> >>
> >> -----------== Posted via Deja News, The Discussion Network ==----------
> >> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
> >
> >
> >
Received on Wed Mar 17 1999 - 20:05:40 CST