Re: Dynamic views for datafiles and tablespaces

Dino,

V$DATAFILE is dynamic performance view which contains datafile information from the control file.
STATUS describes type of file (system or user) and its status. Values: OFFLINE, ONLINE, SYSTEM, RECOVER, SYSOFF (an offline file from the SYSTEM tablespace)

DBA_DATA_FILES is static data dictionary view which decribes data files. STATUS can be either AVAILABLE or INVALID (INVALID means that that the file number is not in use, e.g. a file in a tablespace that was dropped)

Dario

"Dino Hsu" <dino1.nospam_at_ms1.hinet.net> wrote in message news:67rpotkii7a7r94s2f5bngnha3v4f99cjc_at_4ax.com...
> Dear all,
>
> When studying recovery, we most often talk about manipulations of
> datafiles and tablespaces. Although tablespace is a 'logical
> structure', it is still quite physical for me because one tablespace
> contains one or more datafiles, and 'datafile' is a 'physical
> structure'. Naturally I will compare their dynamic views:
>
> SQL> desc dba_tablespaces
> Name Null? Type
> ----------------------------- -------- --------------------
> TABLESPACE_NAME NOT NULL VARCHAR2(30)
> INITIAL_EXTENT NUMBER
> NEXT_EXTENT NUMBER
> MIN_EXTENTS NOT NULL NUMBER
> MAX_EXTENTS NUMBER
> PCT_INCREASE NUMBER
> MIN_EXTLEN NUMBER
> STATUS VARCHAR2(9)
> CONTENTS VARCHAR2(9)
> LOGGING VARCHAR2(9)
> EXTENT_MANAGEMENT VARCHAR2(10)
> ALLOCATION_TYPE VARCHAR2(9)
> PLUGGED_IN VARCHAR2(3)
>
> SQL> desc v$tablespace
> Name Null? Type
> ----------------------------- -------- --------------------
> TS# NUMBER
> NAME VARCHAR2(30)
>
> 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
>
> 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
>
> Unlike the other three, v$tablespace only has two columns, it is
> virtually a mapping between tablespace name and tablespace number
> (ts#). Without doubt, dba_data_files provides the fundamental
> information about datafiles. However, both dba_tablespaces and
> v$tablespace have many columns, we might have trouble deciding which
> one to use. Moreover, the status columns from the two views give
> different information:
>
> SQL> select file_name, status from dba_data_files;
>
> FILE_NAME STATUS
> ---------------------------------------- ---------
> D:\ORACLE\ORADATA\O8I_TEST\USERS01.DBF AVAILABLE
> D:\ORACLE\ORADATA\O8I_TEST\DR01.DBF AVAILABLE
> D:\ORACLE\ORADATA\O8I_TEST\TOOLS01.DBF AVAILABLE
> D:\ORACLE\ORADATA\O8I_TEST\INDX01.DBF AVAILABLE
> D:\ORACLE\ORADATA\O8I_TEST\RBS01.DBF AVAILABLE
> D:\ORACLE\ORADATA\O8I_TEST\TEMP01.DBF AVAILABLE
> D:\ORACLE\ORADATA\O8I_TEST\SYSTEM01.DBF AVAILABLE
>
> 7 rows selected.
>
> SQL> select name, status from v$datafile;
>
> NAME STATUS
> ---------------------------------------- -------
> D:\ORACLE\ORADATA\O8I_TEST\SYSTEM01.DBF SYSTEM
> D:\ORACLE\ORADATA\O8I_TEST\RBS01.DBF ONLINE
> D:\ORACLE\ORADATA\O8I_TEST\USERS01.DBF OFFLINE
> D:\ORACLE\ORADATA\O8I_TEST\TEMP01.DBF ONLINE
> D:\ORACLE\ORADATA\O8I_TEST\TOOLS01.DBF ONLINE
> D:\ORACLE\ORADATA\O8I_TEST\INDX01.DBF ONLINE
> D:\ORACLE\ORADATA\O8I_TEST\DR01.DBF ONLINE
>
> 7 rows selected.
>
> The users01.dbf is purposely switched offline, as is shown by
> v$datafile, while dba_data_files says it is mysteriously 'AVAILABLE'.
> Anyone out there can show me how to use these view? Thanks in advance.
>
> Dino
>
Received on Wed Aug 29 2001 - 08:52:36 CDT