[20121227]v$datafile访问是从数据文件获取信息吗.txt
记得以前开始学习oracle的时候,被问及访问v$datafile从哪里获取信息,当时想都没想,从数据文件.
虽然当时对方并没有告诉答案,显然认为水平不再同一档次上.
直到有一次看一篇blog:
http://avdeo.com/2008/05/29/oracle-database-recovery-details/
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.