| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: V$DATAFILE columns
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01BFD246.34D88DE0
Content-Type: text/plain;
charset="iso-8859-1"
If you are on 8i you can use v$tablespace
Alex Hillman
-----Original Message-----
From: Rachel Carmichael [mailto:carmichr_at_hotmail.com]
Sent: Friday, June 09, 2000 10:12 AM
To: Multiple recipients of list ORACLE-L
Subject: 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).
-- Author: Rachel Carmichael INET: carmichr_at_hotmail.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). ------_=_NextPart_001_01BFD246.34D88DE0 Content-Type: text/html; charset="iso-8859-1" <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1"> <META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2448.0"> <TITLE>RE: V$DATAFILE columns</TITLE> </HEAD> <BODY> <P><FONT SIZE=2>If you are on 8i you can use v$tablespace</FONT> </P> <P><FONT SIZE=2>Alex Hillman</FONT> </P> <P><FONT SIZE=2>-----Original Message-----</FONT> <BR><FONT SIZE=2>From: Rachel Carmichael [<A HREF="mailto:carmichr_at_hotmail.com">mailto:carmichr_at_hotmail.com</A>]</FONT> <BR><FONT SIZE=2>Sent: Friday, June 09, 2000 10:12 AM</FONT> <BR><FONT SIZE=2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=2>Subject: RE: V$DATAFILE columns</FONT> </P> <BR> <P><FONT SIZE=2>if the database is in mount, the dba_data_files view (NOT V$dba_data_files!) </FONT> <BR><FONT SIZE=2>is not available. That I know of, the tablespace_name does not appear in any </FONT> <BR><FONT SIZE=2>of the v$ views, but I have been known to be wrong before.</FONT> </P> <P><FONT SIZE=2>IF you have followed a naming convention such as including the tablespace </FONT> <BR><FONT SIZE=2>name in the name of the external datafile, you might be able to figure out </FONT> <BR><FONT SIZE=2>which tablespace it belongs to.</FONT> </P> <P><FONT SIZE=2>But to rename a datafile you don't need to know the tablespace it belongs </FONT> <BR><FONT SIZE=2>to. The command is:</FONT> </P> <P><FONT SIZE=2>alter database rename 'old file name' to 'newfilename';</FONT> </P> <P><FONT SIZE=2>then you have to exit and physically move or copy the datafile from the old </FONT> <BR><FONT SIZE=2>location to the new one. Once that is done, you can start the database and </FONT> <BR><FONT SIZE=2>it will recognize where the datafile now is.</FONT> </P> <P><FONT SIZE=2>As for reference material on the V$ views.... Oracle docs. The Server </FONT> <BR><FONT SIZE=2>Reference manual has the init.ora parameters, the dba/all/user _ views</FONT> <BR><FONT SIZE=2>and the v$ views. Not completely clear, but it does list the name, what it </FONT> <BR><FONT SIZE=2>is meant to contain, the columns and what they are meant to contain.</FONT> </P> <P><FONT SIZE=2>hth</FONT> </P> <P><FONT SIZE=2>Rachel</FONT> </P> <BR> <BR> <P><FONT SIZE=2>>From: Rahul <rahul_at_ratelindo.co.id></FONT> <BR><FONT SIZE=2>>Reply-To: ORACLE-L_at_fatcity.com</FONT> <BR><FONT SIZE=2>>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com></FONT> <BR><FONT SIZE=2>>Subject: RE: V$DATAFILE columns</FONT> <BR><FONT SIZE=2>>Date: Fri, 09 Jun 2000 01:04:11 -0800</FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>>you could join v$filestat and v$dba_data_files</FONT> <BR><FONT SIZE=2>>to get the tablespace name</FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>>--------------+--------------+--------------+--------------------</FONT> <BR><FONT SIZE=2>>select substr(file_name,1,40) file_name,</FONT> <BR><FONT SIZE=2>> PHYRDS PHYRD,</FONT> <BR><FONT SIZE=2>> PHYWRTS PHYWR,</FONT> <BR><FONT SIZE=2>> ROUND(PHYBLKRD/decode(PHYRDS,0,1,PHYRDS),0) PER_READ,</FONT> <BR><FONT SIZE=2>> ROUND(PHYBLKWRT/decode(PHYWRTS,0,1,PHYWRTS),0) PER_WRITE,</FONT> <BR><FONT SIZE=2>> tablespace_name</FONT> <BR><FONT SIZE=2>>from v$filestat, dba_data_files</FONT> <BR><FONT SIZE=2>>where file#= file_id</FONT> <BR><FONT SIZE=2>>order by 3</FONT> <BR><FONT SIZE=2>>/</FONT> <BR><FONT SIZE=2>>--------------+--------------+--------------+--------------------</FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>>query the v$fixed_view_definition to get info on v$ views...</FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>>HTH</FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>>rahul</FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>> > ----------</FONT> <BR><FONT SIZE=2>> > From: Linda Hagedorn[SMTP:Linda_at_pets.com ]</FONT> <BR><FONT SIZE=2>> > Reply To: ORACLE-L_at_fatcity.com</FONT> <BR><FONT SIZE=2>> > Sent: 09 Juni 2000 03:31</FONT> <BR><FONT SIZE=2>> > To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=2>> > Subject: V$DATAFILE columns</FONT> <BR><FONT SIZE=2>> ></FONT> <BR><FONT SIZE=2>> > Hello,</FONT> <BR><FONT SIZE=2>> ></FONT> <BR><FONT SIZE=2>> > I have a database in mount, and need to rename a dataset. Will someone </FONT> <BR><FONT SIZE=2>>let</FONT> <BR><FONT SIZE=2>> > me know the column name in V$DATAFILE which holds the external dataset </FONT> <BR><FONT SIZE=2>>name,</FONT> <BR><FONT SIZE=2>> > and the tablespace name?</FONT> <BR><FONT SIZE=2>> ></FONT> <BR><FONT SIZE=2>> > Is there a reference which lists all the V$ views, all the columns in </FONT> <BR><FONT SIZE=2>>the V$</FONT> <BR><FONT SIZE=2>> > views, and if there's a V$_view_columns (of sorts) view where I can list </FONT> <BR><FONT SIZE=2>>all</FONT> <BR><FONT SIZE=2>> > the columns for a view?</FONT> <BR><FONT SIZE=2>> ></FONT> <BR><FONT SIZE=2>> > Any reference to doc is sincerely appreciated.</FONT> <BR><FONT SIZE=2>> ></FONT> <BR><FONT SIZE=2>> > Linda</FONT> <BR><FONT SIZE=2>> > --</FONT> <BR><FONT SIZE=2>> > Author: Linda Hagedorn</FONT> <BR><FONT SIZE=2>> > INET: Linda_at_pets.com</FONT> <BR><FONT SIZE=2>> ></FONT> <BR><FONT SIZE=2>> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=2>> > San Diego, California -- Public Internet access / Mailing Lists</FONT> <BR><FONT SIZE=2>> > --------------------------------------------------------------------</FONT> <BR><FONT SIZE=2>> > To REMOVE yourself from this mailing list, send an E-Mail message</FONT> <BR><FONT SIZE=2>> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in</FONT> <BR><FONT SIZE=2>> > the message BODY, include a line containing: UNSUB ORACLE-L</FONT> <BR><FONT SIZE=2>> > (or the name of mailing list you want to be removed from). You may</FONT> <BR><FONT SIZE=2>> > also send the HELP command for other information (like subscribing).</FONT> <BR><FONT SIZE=2>> ></FONT> <BR><FONT SIZE=2>>--</FONT> <BR><FONT SIZE=2>>Author: Rahul</FONT> <BR><FONT SIZE=2>> INET: rahul_at_ratelindo.co.id</FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=2>>San Diego, California -- Public Internet access / Mailing Lists</FONT> <BR><FONT SIZE=2>>--------------------------------------------------------------------</FONT> <BR><FONT SIZE=2>>To REMOVE yourself from this mailing list, send an E-Mail message</FONT> <BR><FONT SIZE=2>>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in</FONT> <BR><FONT SIZE=2>>the message BODY, include a line containing: UNSUB ORACLE-L</FONT> <BR><FONT SIZE=2>>(or the name of mailing list you want to be removed from). You may</FONT> <BR><FONT SIZE=2>>also send the HELP command for other information (like subscribing).</FONT> </P> <P><FONT SIZE=2>________________________________________________________________________</FONT> <BR><FONT SIZE=2>Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com</FONT> </P> <P><FONT SIZE=2>-- </FONT> <BR><FONT SIZE=2>Author: Rachel Carmichael</FONT> <BR><FONT SIZE=2> INET: carmichr_at_hotmail.com</FONT> </P> <P><FONT SIZE=2>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=2>San Diego, California -- Public Internet access / Mailing Lists</FONT> <BR><FONT SIZE=2>--------------------------------------------------------------------</FONT> <BR><FONT SIZE=2>To REMOVE yourself from this mailing list, send an E-Mail message</FONT> <BR><FONT SIZE=2>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in</FONT> <BR><FONT SIZE=2>the message BODY, include a line containing: UNSUB ORACLE-L</FONT>Received on Fri Jun 09 2000 - 14:09:16 CDT
 |
 |