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

In article <7cp5fm$b56_at_news.abbott.com>,   "Graham C Thornton" <graham.thornton_at_ln.ssw.abbott.com> 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
> >
> >
> >
>
>

I assume that u have DBA privileges.

well u know that u can specify the TABLESPACE NAME when u create a table.

Assume the tablespace has 3 datafiles f1,f2,f3 and u want to create the table on f2.

U shud have enough space on it.

What u shoud do first of all is to make the other 2 data files offline.

Im not sure the command but u can use one of these ALTER TABLESPACE or ALTER DATABASE and theres one option to say alter ... datafile 'xxxxx' OFFLINE.

and then try to create the table with tablespace as this. table will b created in the datafile f2.

-----------== 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:18:17 CST