v$datafile_header相关字段的使用：

◆1、fuzzy也是用于表示数据文件 status的一个选项。

◆2、checkpoint_time用于查看何时发生了检查点，可用于判断一个操作是否会发出检查点。

在v9.0.1及之前的版本,fuzzy可以用于标识数据文件是不是处于hot backup状态。

当一个数据文件begin backup 时,fuzzy列即为yes,当end backup时这一列又被置为null

在9.2之后的版本，当数据库打开后，fuzzy这一列便为yes了

当数据文件为read write状态且为online时，则fuzzy列为yes

当数据文件为read only，则fuzzy为no

当数据文件为offline，则fuzzy为null

sql> select * from v$version;

banner

----------------------------------------------------------------

oracle9i release 9.2.0.1.0 - production

pl/sql release 9.2.0.1.0 - production

core 9.2.0.1.0 production

tns for 32-bit windows: version 9.2.0.1.0 - production

nlsrtl version 9.2.0.1.0 - production

sql> select * from v$tablespace;

ts# name inc

---------- ------------------------------ ---

0 system yes

1 undotbs1 yes

2 temp yes

3 indx yes

4 tools yes

5 users yes

6 cattbs yes

已选择7行。

sql> select status,fuzzy,tablespace_name,name from v$datafile_header;

status fuz tablespace_name name

------- --- ------------------------------ -----------------------------------

online yes system e:oracleoradatarcatsystem01.dbf

online yes undotbs1 e:oracleoradatarcatundotbs01.dbf

online yes indx e:oracleoradatarcatindx01.dbf

online yes tools e:oracleoradatarcattools01.dbf

online yes users e:oracleoradatarcatusers01.dbf

online yes cattbs e:oracleoradatarcatcattbs01.dbf

已选择6行。

sql> alter tablespace users read only;

表空间已更改。

sql> select status,fuzzy,tablespace_name,name from v$datafile_header;

status fuz tablespace_name name

------- --- ------------------------------ -----------------------------------

online yes system e:oracleoradatarcatsystem01.dbf

online yes undotbs1 e:oracleoradatarcatundotbs01.dbf

online yes indx e:oracleoradatarcatindx01.dbf

online yes tools e:oracleoradatarcattools01.dbf

online no users e:oracleoradatarcatusers01.dbf

online yes cattbs e:oracleoradatarcatcattbs01.dbf

已选择6行。

sql> alter tablespace users read write;

表空间已更改。

sql> select status,fuzzy,tablespace_name,name from v$datafile_header;

status fuz tablespace_name name

------- --- ------------------------------ -----------------------------------

online yes system e:oracleoradatarcatsystem01.dbf

online yes undotbs1 e:oracleoradatarcatundotbs01.dbf

online yes indx e:oracleoradatarcatindx01.dbf

online yes tools e:oracleoradatarcattools01.dbf

online yes users e:oracleoradatarcatusers01.dbf

online yes cattbs e:oracleoradatarcatcattbs01.dbf

已选择6行。

sql> alter tablespace users offline;

表空间已更改。

sql> select status,fuzzy,tablespace_name,name from v$datafile_header;

status fuz tablespace_name name

------- --- ------------------------------ --------------------------------

online yes system e:oracleoradatarcatsystem01.dbf

online yes undotbs1 e:oracleoradatarcatundotbs01.dbf

online yes indx e:oracleoradatarcatindx01.dbf

online yes tools e:oracleoradatarcattools01.dbf

offline

online yes cattbs e:oracleoradatarcatcattbs01.dbf

已选择6行。

sql> alter tablespace users online;

表空间已更改。

sql> select status,fuzzy,tablespace_name,name from v$datafile_header;

status fuz tablespace_name name

------- --- ------------------------------ ----------------------------------

online yes system e:oracleoradatarcatsystem01.dbf

online yes undotbs1 e:oracleoradatarcatundotbs01.dbf

online yes indx e:oracleoradatarcatindx01.dbf

online yes tools e:oracleoradatarcattools01.dbf

online yes users e:oracleoradatarcatusers01.dbf

online yes cattbs e:oracleoradatarcatcattbs01.dbf

已选择6行。

通过checkpoint_time字段，可以判断是否发生检查点

sql> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

会话已更改。

sql> select status,fuzzy,tablespace_name,checkpoint_time from v$datafile_header;

status fuz tablespace_name checkpoint_time

------- --- ------------------------------ -------------------

online yes system 2008-02-28 08:34:20

online yes undotbs1 2008-02-28 08:34:20

online yes indx 2008-02-28 08:34:20

online yes tools 2008-02-28 08:34:20

online yes users 2008-02-28 10:07:28

online yes cattbs 2008-02-28 08:34:20

已选择6行。

sql> alter system suspend;

系统已更改。

sql> alter system resume;

系统已更改。

sql> select status,fuzzy,tablespace_name,checkpoint_time from v$datafile_header;

status fuz tablespace_name checkpoint_time

------- --- ------------------------------ -------------------

online yes system 2008-02-28 08:34:20

online yes undotbs1 2008-02-28 08:34:20

online yes indx 2008-02-28 08:34:20

online yes tools 2008-02-28 08:34:20

online yes users 2008-02-28 10:07:28

online yes cattbs 2008-02-28 08:34:20

已选择6行。