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

From: Graham C Thornton <graham.thornton_at_ln.ssw.abbott.com>
Date: Wed, 17 Mar 1999 15:05:23 -0600
Message-ID: <7cp5fm$b56@news.abbott.com>

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 - 15:05:23 CST