[20121227]v$datafile訪問是從資料檔案獲取資訊嗎.txt

lfree發表於2012-12-28
[20121227]v$datafile訪問是從資料檔案獲取資訊嗎.txt

記得以前開始學習oracle的時候,被問及訪問v$datafile從哪裡獲取資訊,當時想都沒想,從資料檔案.
雖然當時對方並沒有告訴答案,顯然認為水平不再同一檔次上.

直到有一次看一篇blog:


SELECT 'controlfile' "SCN location", 'SYSTEM checkpoint' NAME, checkpoint_change#
  FROM v$database
UNION
SELECT 'file in controlfile', NAME, checkpoint_change#
  FROM v$datafile
 WHERE NAME LIKE '%users01%'
UNION
SELECT 'file header', NAME, checkpoint_change#
  FROM v$datafile_header
 WHERE NAME LIKE '%users01%';

實際上當我拿這個問題問初學者,許多的回答跟我當時一樣.為了加強理解自己做一些例子來說明:


測試很簡單,關閉資料庫,將表空間users對應的資料檔案改名,啟動資料庫到mount狀態,執行前面的指令碼:
$ mv users01.dbf users01.dbf.xxx

SQL> startup mount
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2215064 bytes
Variable Size            1728054120 bytes
Database Buffers          402653184 bytes
Redo Buffers                4964352 bytes
Database mounted.

SQL> column name format a50
SQL> SELECT 'controlfile' "SCN location", 'SYSTEM checkpoint' NAME, checkpoint_change#
UNION
SELECT 'file header', NAME, checkpoint_change#
  FROM v$database
UNION
SELECT 'file in controlfile', NAME, checkpoint_change#
  FROM v$datafile
 WHERE NAME LIKE '%users01%'
UNION
SELECT 'file header', NAME, checkpoint_change#
  FROM v$datafile_header
WHERE NAME LIKE '%users01%';

SCN location        NAME                                               CHECKPOINT_CHANGE#
------------------- -------------------------------------------------- ------------------
controlfile         SYSTEM checkpoint                                          3010736324
file in controlfile /u01/app/oracle11g/oradata/test/users01.dbf                3010736324

--如果v$datafile從資料檔案獲得,已經檔案改名,應該無法獲取資訊,而現在可以查詢到,至少說明不是從資料檔案獲取的.

--實際上資料檔案的SCN是從v$datafile_header獲取的.
SQL> column name format a50
SQL> select file#,status, CHECKPOINT_CHANGE#,name from v$datafile_header;

     FILE# STATUS  CHECKPOINT_CHANGE# NAME
---------- ------- ------------------ --------------------------------------------------
         1 ONLINE          3010736324 /u01/app/oracle11g/oradata/test/system01.dbf
         2 ONLINE          3010736324 /u01/app/oracle11g/oradata/test/sysaux01.dbf
         3 ONLINE          3010736324 /u01/app/oracle11g/oradata/test/undotbs01.dbf
         4 ONLINE                   0
         5 ONLINE          3010736324 /u01/app/oracle11g/oradata/test/example01.dbf
         6 ONLINE          3010736324 /u01/app/oracle11g/oradata/test/rman01.dbf
         7 ONLINE          3010736324 /u01/app/oracle11g/oradata/test/tools01.dbf
         8 ONLINE          3010736324 /u01/app/oracle11g/oradata/test/test01.dbf

8 rows selected.

--如果你把資料檔案全部移走.依舊可以訪問v$datafile檢視.

SQL> select file#,TS#, RFILE#, STATUS ,NAME, CHECKPOINT_CHANGE# from v$datafile ;

     FILE#        TS#     RFILE# STATUS  NAME                                               CHECKPOINT_CHANGE#
---------- ---------- ---------- ------- -------------------------------------------------- ------------------
         1          0          1 SYSTEM  /u01/app/oracle11g/oradata/test/system01.dbf               3010736324
         2          1          2 ONLINE  /u01/app/oracle11g/oradata/test/sysaux01.dbf               3010736324
         3          2          3 ONLINE  /u01/app/oracle11g/oradata/test/undotbs01.dbf              3010736324
         4          4          4 ONLINE  /u01/app/oracle11g/oradata/test/users01.dbf                3010736324
         5          6          5 ONLINE  /u01/app/oracle11g/oradata/test/example01.dbf              3010736324
         6          7          6 ONLINE  /u01/app/oracle11g/oradata/test/rman01.dbf                 3010736324
         7          8          7 ONLINE  /u01/app/oracle11g/oradata/test/tools01.dbf                3010736324
         8          9          8 ONLINE  /u01/app/oracle11g/oradata/test/test01.dbf                 3010736324

8 rows selected.

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-751785/,如需轉載,請註明出處,否則將追究法律責任。

相關文章