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
 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. |