| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Dynamic views for datafiles and tablespaces
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 AVAILABLED:\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:40:56 CDT
 |
 |