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
---------------- -------- ---------------
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
------------------------ -------- -----------
Biju Thomas is Oracle7.3 OCP, Oracle8 OCP,
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA
Questions/Comments? Write to firstname.lastname@example.org.
© The scripts, tips and articles appearing on BijooS.com cannot be reproduced
elsewhere without the prior permission from the webmaster.