New Columns in the DBA_DATA_FILES 
and V$DATAFILE views

Oracle has improved the contents of DBA_DATA_FILES and V$DATAFILE views with lot of useful information. Though we had the AUTOEXTENSIBLE option for data files in Oracle7, the information was not available in either of these views. The one new addition I really like in the V$DATAFILE view is the file creation date and time.

In Oracle8i, Oracle creates the temporary tablespaces really temporary. If you create the tablespace using CREATE TEMPORARY TABLESPACE command, the file belonging to such tablespaces are not entered in the control file and they are not required for recovery. The DBA_DATA_FILES view does not show such files also. Now a new view available named DBA_TEMP_FILES which has exactly the same columns of DBA_DATA_FILES for information on the temp files belonging to the TEMPORARY tablespaces. TEMPORARY tablespaces are always locally managed with UNIFORM extent size (Dictionary managed tablespaces also can be TEMPORARY, but they are created with CREATE TABLESPACE command with TEMPORARY clause, such files are listed in the DBA_DATA_FILES view and are required for recovery).

SQL> desc DBA_DATA_FILES
 Name             Null?    Type
 ---------------- -------- ---------------
 FILE_NAME                 VARCHAR2(513)
 FILE_ID                   NUMBER
 TABLESPACE_NAME           VARCHAR2(30)
 BYTES                     NUMBER
 BLOCKS                    NUMBER
 STATUS                    VARCHAR2(9)
 RELATIVE_FNO              NUMBER
 AUTOEXTENSIBLE            VARCHAR2(3)
 MAXBYTES                  NUMBER
 MAXBLOCKS                 NUMBER
 INCREMENT_BY              NUMBER
 USER_BYTES                NUMBER
 USER_BLOCKS               NUMBER

The seven columns appearing from RELATIVE_FNO are new that were not available in Oracle7.

Look at the columns in V$DATAFILE in 8i, the Oracle7 view had only FILE#, STATUS, ENABLED, CHECKPOINT_CHANGE# , BYTES, CREATE_BYTES and NAME.

SQL> desc V$DATAFILE
 Name                     Null?    Type
 ------------------------ -------- -----------
 FILE#                             NUMBER
 CREATION_CHANGE#                  NUMBER
 CREATION_TIME                     DATE
 TS#                               NUMBER
 RFILE#                            NUMBER
 STATUS                            VARCHAR2(7)
 ENABLED                           VARCHAR2(10)
 CHECKPOINT_CHANGE#                NUMBER
 CHECKPOINT_TIME                   DATE
 UNRECOVERABLE_CHANGE#             NUMBER
 UNRECOVERABLE_TIME                DATE
 LAST_CHANGE#                      NUMBER
 LAST_TIME                         DATE
 OFFLINE_CHANGE#                   NUMBER
 ONLINE_CHANGE#                    NUMBER
 ONLINE_TIME                       DATE
 BYTES                             NUMBER
 BLOCKS                            NUMBER
 CREATE_BYTES                      NUMBER
 BLOCK_SIZE                        NUMBER
 NAME                              VARCHAR2(513)
 PLUGGED_IN                        NUMBER

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.