|
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行。
|
