RE: V$DATAFILE columns

if the database is in mount, the dba_data_files view (NOT V$dba_data_files!) is not available. That I know of, the tablespace_name does not appear in any of the v$ views, but I have been known to be wrong before.

IF you have followed a naming convention such as including the tablespace name in the name of the external datafile, you might be able to figure out which tablespace it belongs to.

But to rename a datafile you don't need to know the tablespace it belongs to. The command is:

alter database rename 'old file name' to 'newfilename';

then you have to exit and physically move or copy the datafile from the old location to the new one. Once that is done, you can start the database and it will recognize where the datafile now is.

As for reference material on the V$ views.... Oracle docs. The Server Reference manual has the init.ora parameters, the dba/all/user _ views and the v$ views. Not completely clear, but it does list the name, what it is meant to contain, the columns and what they are meant to contain.

hth

Rachel

>From: Rahul <rahul_at_ratelindo.co.id>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: V$DATAFILE columns
>Date: Fri, 09 Jun 2000 01:04:11 -0800
>
>you could join v$filestat and v$dba_data_files
>to get the tablespace name
>
>--------------+--------------+--------------+--------------------
>select substr(file_name,1,40) file_name,
> PHYRDS PHYRD,
> PHYWRTS PHYWR,
> ROUND(PHYBLKRD/decode(PHYRDS,0,1,PHYRDS),0) PER_READ,
> ROUND(PHYBLKWRT/decode(PHYWRTS,0,1,PHYWRTS),0) PER_WRITE,
> tablespace_name
>from v$filestat, dba_data_files
>where file#= file_id
>order by 3
>/
>--------------+--------------+--------------+--------------------
>
>query the v$fixed_view_definition to get info on v$ views...
>
>HTH
>
>rahul
>
>
>
> > ----------
> > From: Linda Hagedorn[SMTP:Linda_at_pets.com ]
> > Reply To: ORACLE-L_at_fatcity.com
> > Sent: 09 Juni 2000 03:31
> > To: Multiple recipients of list ORACLE-L
> > Subject: V$DATAFILE columns
> >
> > Hello,
> >
> > I have a database in mount, and need to rename a dataset. Will someone
>let
> > me know the column name in V$DATAFILE which holds the external dataset
>name,
> > and the tablespace name?
> >
> > Is there a reference which lists all the V$ views, all the columns in
>the V$
> > views, and if there's a V$_view_columns (of sorts) view where I can list
>all
> > the columns for a view?
> >
> > Any reference to doc is sincerely appreciated.
> >
> > Linda
> > --
> > Author: Linda Hagedorn
> > INET: Linda_at_pets.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > 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).
> >
>--
>Author: Rahul
> INET: rahul_at_ratelindo.co.id
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>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).