Friday, October 31, 2008

V$DataFile

 V$DATAFILE , V_$DATAFILE , GV$DATAFILE

V_$Datafile  The actual underlaying Dynamic View .V$datafile is a public synonym name of V$_Datafile.

You can use V$datafile to view information about data file related to NON-RAC Environment Use GV$Datafile to view information about datafile related to RAC Environment ,

the difference between V$ and GV$ view is INST_ID column which stores Instance ID information in RAC Environment.

V$DATAFILE reads the data file information from control file, some important information you can obtain from V$DATAFILE are  Data file status( OFFLINE, ONLINE, SYSTEM, RECOVE ), SCN at last checkpoint , Size when created (in bytes)  , weather this data file is enabled for DML operations or Not (see  ENABLED column status)

Weirdness of v$datafile on a standby db.


V$DATAFILE and Standby Database.

On a standby database "STATUS" column wouldn't get updated in some scenarios. All data files created after the standby control file creation timestamp would get "RECOVER" status as they get pushed to the standby database. Solution to this problem is to query V$datafile_header . Whenever we query v$datafile_header, Oracle retrieve the all data file header blocks, and it gives us the right "STATUS" of data files.


Related Tables or Dynamic views

file$

ts$

x$ktfbhc

dba_data_files

dba_temp_files

gv$dbfile

dba_free_space

v$datafile_header

To know the view definition V$DATAFILE you can simple query

0 comments: