左右v$datafile和v$tempfile中间file#
v$datafile关于存储在文件中的数据视图的信息,v$tempfile查看存储在一个临时文件中的信息。
有两种观点file#现场,首先来看看官方文件的定义:
V$DATAFILE
This view contains datafile information from the control file.
See Also:
"V$DATAFILE_HEADER", which displays information from datafile headers
Column | Datatype | Description |
---|---|---|
FILE# |
NUMBER |
File identification number |
CREATION_CHANGE# |
NUMBER |
Change number at which the datafile was created |
CREATION_TIME |
DATE |
Timestamp of the datafile creation |
TS# |
NUMBER |
Tablespace number |
RFILE# |
NUMBER |
Tablespace relative datafile number |
STATUS |
VARCHAR2(7) |
Type of file (system or user) and its status. Values: OFFLINE , ONLINE , SYSTEM , RECOVER , SYSOFF (anoffline file from the SYSTEM tablespace) |
ENABLED |
VARCHAR2(10) |
Describes how accessible the file is from SQL:
|
CHECKPOINT_CHANGE# |
NUMBER |
SCN at last checkpoint |
CHECKPOINT_TIME |
DATE |
Timestamp of the checkpoint# |
UNRECOVERABLE_CHANGE# |
NUMBER |
Last unrecoverable change number made to this datafile. If the database is in ARCHIVELOG mode, then this column is updated when an unrecoverable operationcompletes. If the database is not in ARCHIVELOG mode, this column does not get updated. |
UNRECOVERABLE_TIME |
DATE |
Timestamp of the last unrecoverable change. This column is updated only if the database is in ARCHIVELOG mode. |
LAST_CHANGE# |
NUMBER |
Last change number made to this datafile (null if the datafile is being changed) |
LAST_TIME |
DATE |
Timestamp of the last change |
OFFLINE_CHANGE# |
NUMBER |
Offline change number of the last offline range. This column is updated only when the datafile is brought online. |
ONLINE_CHANGE# |
NUMBER |
Online change number of the last offline range |
ONLINE_TIME |
DATE |
Online timestamp of the last offline range |
BYTES |
NUMBER |
Current datafile size (in bytes); 0 if inaccessible |
BLOCKS |
NUMBER |
Current datafile size (in blocks); 0 if inaccessible |
CREATE_BYTES |
NUMBER |
Size when created (in bytes) |
BLOCK_SIZE |
NUMBER |
Block size of the datafile |
NAME |
VARCHAR2(513) |
Name of the datafile |
PLUGGED_IN |
NUMBER |
Describes whether the tablespace is plugged in. The value is 1 if the tablespace is plugged in and has not been made read/write, 0 ifnot. |
BLOCK1_OFFSET |
NUMBER |
Offset from the beginning of the file to where the Oracle generic information begins. The exact length of the file can be computed as follows:BYTES + BLOCK1_OFFSET . |
AUX_NAME |
VARCHAR2(513) |
Auxiliary name that has been set for this file via CONFIGURE AUXNAME |
FIRST_NONLOGGED_SCN |
NUMBER |
First nonlogged SCN |
FIRST_NONLOGGED_TIME |
DATE |
First nonlogged time |
V$TEMPFILE
This view displays tempfile information.
Column | Datatype | Description |
---|---|---|
FILE# |
NUMBER |
Absolute file number |
CREATION_CHANGE# |
NUMBER |
Creation System Change Number (SCN) |
CREATION_TIME |
DATE |
Creation time |
TS# |
NUMBER |
Tablespace number |
RFILE# |
NUMBER |
Relative file number in the tablespace |
STATUS |
VARCHAR2(7) |
Status of the file (OFFLINE |ONLINE ) |
ENABLED |
VARCHAR2(10) |
Enabled for read and/or write |
BYTES |
NUMBER |
Size of the file in bytes (from the file header) |
BLOCKS |
NUMBER |
Size of the file in blocks (from the file header) |
CREATE_BYTES |
NUMBER |
Creation size of the file (in bytes) |
BLOCK_SIZE |
NUMBER |
Block size for the file |
NAME |
VARCHAR2(513) |
Name of the file |
从上面的文档能够看出。在v$tempfile和v$datafile中file#均代表绝对文件好,而rfile#代表相对文件号,以下来验证一下:
SQL> l
1* select file#,name from v$tempfile
SQL> / FILE# NAME
---------- ------------------------------------------------------------
1 /home/app/oraten/oradata/oraten/temp01.dbf
SQL> select file#,name from v$datafile; FILE# NAME
---------- ------------------------------------------------------------
1 /home/app/oraten/oradata/oraten/system01.dbf
2 /home/app/oraten/oradata/oraten/undotbs01.dbf
3 /home/app/oraten/oradata/oraten/sysaux01.dbf
4 /home/app/oraten/oradata/oraten/users01.dbf
5 /home/app/oraten/oradata/oraten/test01.dbf
在v$datafile和v$tempfile中都存在绝对文件号为1的文件。这显然是不正确的。
我们知道在文件无法訪问时,oracle会报错并在错误信息中显示有关文件的信息,以下我们来模拟暂时文件的故障
[oraten@yue oraten]$ chmod 000 temp01.dbf
[oraten@yue oraten]$ ll
总用量 932180
-rw-r----- 1 oraten dba 7061504 11月 7 13:59 control01.ctl
-rw-r----- 1 oraten dba 7061504 11月 7 13:59 control02.ctl
-rw-r----- 1 oraten dba 7061504 11月 7 13:59 control03.ctl
-rw-r----- 1 oraten dba 52429312 11月 7 13:44 redo01.log
-rw-r----- 1 oraten dba 52429312 11月 7 13:54 redo02.log
-rw-r----- 1 oraten dba 52429312 11月 7 13:44 redo03.log
-rw-r----- 1 oraten dba 272637952 11月 7 13:49 sysaux01.dbf
-rw-r----- 1 oraten dba 461381632 11月 7 13:49 system01.dbf
---------- 1 oraten dba 20979712 11月 7 13:39 temp01.dbf
-rw-r----- 1 oraten dba 10493952 11月 7 13:44 test01.dbf
-rw-r----- 1 oraten dba 26222592 11月 7 13:49 undotbs01.dbf
-rw-r----- 1 oraten dba 5251072 11月 7 13:44 users01.dbf
强制启动数据库
SQL> startup force
ORACLE instance started. Total System Global Area 134217728 bytes
Fixed Size 2094544 bytes
Variable Size 88082992 bytes
Database Buffers 37748736 bytes
Redo Buffers 6291456 bytes
Database mounted.
Database opened.
SQL>
创建暂时表。并插入数据
SQL> insert into t1 select * from user_tables;
insert into t1 select * from user_tables
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 101 - see DBWR trace file
ORA-01110: data file 101: '/home/app/oraten/oradata/oraten/temp01.dbf'
呵呵,暂时文件无法訪问,在这里显示的文件号为101,为什么那?
我们将參数 db_files改动为200,继续实验
SQL> show parameter db_files NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_files integer 100
SQL> alter system set db_files=200 scope=spfile; System altered. SQL> startup force
ORACLE instance started. Total System Global Area 134217728 bytes
Fixed Size 2094544 bytes
Variable Size 88082992 bytes
Database Buffers 37748736 bytes
Redo Buffers 6291456 bytes
Database mounted.
Database opened.
SQL> insert into t1 select * from user_tables;
insert into t1 select * from user_tables
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/home/app/oraten/oradata/oraten/temp01.dbf'
原来,v$tempfile中的file#并非绝对文件号。暂时文件的绝对文件号是file#+db_files參数。
打完手工!
!
版权声明:本文博主原创文章。博客,未经同意不得转载。
最新文章
- php案列4
- CSS3动画(个人理解)
- 找到一款不错的网站压力测试工具webbench
- 关于Windows下安装mongodb和加入Windows系统启动项
- LinkIssue: Error 'LINK : fatal error LNK1123: failure during conversion to COFF: file invalid or cor
- 在Salesforce中添加Workflow Rule
- FCKeditor 2.6.4.1 初始化值不能显示中文问题
- js 组件的写法
- YII 创建后台模块
- 初探R语言——R语言笔记
- Qwerty78 Trip(组合数,规律,逆元)
- 成本卷积报错:CSTPSCEX.explode_sc_cost_flags():40:ORA-01476: 除数为 0
- 获取Portal中POWL程序的APPLID
- twitter的ID生成器的snowFlake算法的自造版
- API 自动化框架
- git 查看提交历史
- Ubuntu 14.10 下Hadoop HttpFS 配置
- MySQL更新优化(转)
- 解决xib布局方式支持ios6,ios7
- Web工程中各类地址的写法
热门文章
- Mega的简单使用
- 谷歌Web中国开发手册:1目的&;amp;夹
- Android 之流媒体播放器,广播侧下方这么简单。
- NYOJ 47 河问题
- BDB (Berkeley DB)简要数据库(转载)
- Type mismatch: cannot convert from Enumeration&;lt;String&;gt; to Enumeration&;lt;Object&;gt;
- ios ios7 取消控制拉升
- CopyOnWriteArrayList源代码阅读器
- 在linux上创建nfs遇到的问题。
- Asp.NET MVC3 使用 SignalR 实现推(持续)