我一般查询文件大小的时候用到如下表或者视图file$,v$datafile,v$datafile_header,dba_data_files。
这几个图显示的文件大小是某些时候是有区别的。
一、文件信息的来源
1、file$表
对于本地管理的表空间 表file$其实只是记录了文件初始创建的大小,一旦文件被RESIZE过,那么FILE$表中显示的文件大小就和实际不符合了,
这个时候需要查询表sys.x$ktfbhc(表的含义Kernel Tablespace File Bitmap Header Control)来得到文件的实际大小。
- SQL> create tablespace filetest datafile '/u01/app/oracle/oradata/huateng/file01.dbf' size 8192000;
- Tablespace created.
- SQL> select file#,blocks,spare1 from file$;
- FILE# BLOCKS SPARE1
- ---------- ---------- ----------
- 1 64000 4194306
- 2 51200 8388610
- 3 3200 12582914
- 4 640 16777218
- 5 12800 20971522
- 6 1280 25165826
- 7 1000 29360130
- 7 rows selected.
- SQL> select KTFBHCAFNO,KTFBHCSZ from sys.x$ktfbhc;
- KTFBHCAFNO KTFBHCSZ
- ---------- ----------
- 1 88320
- 2 62720
- 3 10880
- 4 640
- 5 12800
- 6 5120
- 7 1000
- 7 rows selected.
- SQL> select 2000*8192 from dual;
- 2000*8192
- ----------
- 16384000
- SQL> alter database datafile 7 resize 16384000;
- Database altered.
- SQL> select file#,blocks,spare1 from file$;
- FILE# BLOCKS SPARE1
- ---------- ---------- ----------
- 1 64000 4194306
- 2 51200 8388610
- 3 3200 12582914
- 4 640 16777218
- 5 12800 20971522
- 6 1280 25165826
- 7 1000 29360130
- 7 rows selected.
- SQL> select KTFBHCAFNO,KTFBHCSZ from sys.x$ktfbhc;
- KTFBHCAFNO KTFBHCSZ
- ---------- ----------
- 1 88320
- 2 62720
- 3 10880
- 4 640
- 5 12800
- 6 5120
- 7 2000
- 7 rows selected.
2、V$DATAFILE_HEADER视图
This view displays datafile information from the datafile headers.
这个视图的信息来自数据文件头。这个视图实际是基于X$KCVFH创建的。
kcvfh是描述数据文件头的一个C结构体,BBED可以查看
- BBED>
- BBED> map /v
- File: /u01/app/oracle/oradata/huateng/htyansp01.dbf (0)
- Block: 1 Dba:0x00000000
- ------------------------------------------------------------
- Data File Header
- struct kcvfh, 860 bytes @0
- struct kcvfhbfh, 20 bytes @0
- struct kcvfhhdr, 76 bytes @20
- ub4 kcvfhrdb @96
- struct kcvfhcrs, 8 bytes @100
- ub4 kcvfhcrt @108
- ub4 kcvfhrlc @112
- struct kcvfhrls, 8 bytes @116
- ub4 kcvfhbti @124
- struct kcvfhbsc, 8 bytes @128
- ub2 kcvfhbth @136
- ub2 kcvfhsta @138
- struct kcvfhckp, 36 bytes @484
- ub4 kcvfhcpc @140
- ub4 kcvfhrts @144
- ub4 kcvfhccc @148
- struct kcvfhbcp, 36 bytes @152
- ub4 kcvfhbhz @312
- ................................
- ...............................
- 省略其他。
因此这里显示的文件大小实际是来自文件头记录的文件大小。
如下:
- SQL> set autot traceonly exp
- SQL> select * from v$datafile_header;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 2441152905
- ----------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ----------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 533 | 0 (0)| 00:00:01 |
- |* 1 | FIXED TABLE FULL| X$KCVFH | 1 | 533 | 0 (0)| 00:00:01 |
- ----------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter("INST_ID"=USERENV('INSTANCE'))
- SQL> set autot off
- SQL> select hxfil,fhfsz from x$kcvfh;
- HXFIL FHFSZ
- ---------- ----------
- 1 88320
- 2 62720
- 3 10880
- 4 640
- 5 12800
- 6 5120
- 7 2000
- 7 rows selected.
3、V$DATAFILE视图
This view contains datafile information from the control file.
联机文档说这部分信息来自控制文件。
其实这个说法并不完全对,至少文件大小也是来自文件头x$kcvfh表。
通过这个视图的建立语句就可以得到视图的blocks来自fh.fhfsz(fh是x$kcvfh的别名)
- select fe.inst_id,fe.fenum,to_number(fe.fecrc_scn), to_date(fe.fecrc_tim,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), fe.fetsn,fe.ferfn, decode(fe.fetsn,0,decode
- (bitand(fe.festa,2),0,'SYSOFF','SYSTEM'), decode(bitand(fe.festa,18),0,'OFFLINE',2,'ONLINE','RECOVER')), decode(fe.fedor,2,'READ ONLY
- ', decode(bitand(fe.festa, 12), 0,'DISABLED',4,'READ ONLY',12,'READ WRITE','UNKNOWN')), to_number(fe.fecps), to_date(fe.fecpt,'MM/DD/RR
- HH24:MI:SS','NLS_CALENDAR=Gregorian'), to_number(fe.feurs), to_date(fe.feurt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), to_number(fe.fests), decode(fe.f
- ests,NULL,to_date(NULL), to_date(fe.festt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian')), to_number(fe.feofs),to_number(fe.feonc_scn), to_date
- (fe.feonc_tim,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), fh.fhfsz*fe.febsz,fh.fhfsz,fe.fecsz*fe.febsz,fe.febsz,fn.fnnam, fe.fefdb, fn.fnbof, decode(
- fe.fepax, 0, 'UNKNOWN', 65535, 'NONE', fnaux.fnnam), to_number(fh.fhfirstunrecscn), to_date(fh.fhfirstunrectime,'MM/DD/RR
- HH24:MI:SS','NLS_CALENDAR=Gregorian'), fe.fepdi, fe.fefcrs, fe.fefcrt, decode(fe.fefdb, 1, 'YES', 'NO'), fe.feplus, fe.feprls, fe.feprlt fr
- om x$kccfe fe, x$kccfn fn, x$kccfn fnaux, x$kcvfh fh where ((fe.fepax!=65535 and fe.fepax!=0 and
- fe.fepax=fnaux.fnnum) or ((fe.fepax=65535 or fe.fepax=0) and fe.fenum=fnaux.fnfno and fnaux.fntyp=4 and
- fnaux.fnnam is not null and bitand(fnaux.fnflg, 4) != 4 and fe.fefnh=fnaux.fnnum))
- and fn.fnfno=fe.fenum and fn.fnfno=fh.hxfil and fe.fefnh=fn.fnnum and fe.fedup!=0 and
- fn.fntyp=4 and fn.fnnam is not null and bitand(fn.fnflg, 4) != 4 order by fe.fenum
4、DBA_DATA_FILES视图
这个视图的信息是2部分UNION ALL起来的,也就是字典管理表空间和本地管理表空间2部分。
这个表的创建视图如下:
- select v.name, f.file#, ts.name,
- ts.blocksize * f.blocks, f.blocks,
- decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
- f.relfile#, decode(f.inc, 0, 'NO', 'YES'),
- ts.blocksize * f.maxextend, f.maxextend, f.inc,
- ts.blocksize * (f.blocks - 1), f.blocks - 1,
- decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
- decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER'))
- from sys.file$ f, sys.ts$ ts, sys.v$dbfile v, x$kccfe fe
- where v.file# = f.file#
- and f.spare1 is NULL
- and f.ts# = ts.ts#
- and fe.fenum = f.file#
- union all
- select
- v.name,f.file#, ts.name,
- decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcsz, NULL),
- decode(hc.ktfbhccval, 0, hc.ktfbhcsz, NULL),
- decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
- f.relfile#,
- decode(hc.ktfbhccval, 0, decode(hc.ktfbhcinc, 0, 'NO', 'YES'), NULL),
- decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcmaxsz, NULL),
- decode(hc.ktfbhccval, 0, hc.ktfbhcmaxsz, NULL),
- decode(hc.ktfbhccval, 0, hc.ktfbhcinc, NULL),
- decode(hc.ktfbhccval, 0, hc.ktfbhcusz * ts.blocksize, NULL),
- decode(hc.ktfbhccval, 0, hc.ktfbhcusz, NULL),
- decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
- decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER'))
- from sys.v$dbfile v, sys.file$ f, sys.x$ktfbhc hc, sys.ts$ ts, x$kccfe fe
- where v.file# = f.file#
- and f.spare1 is NOT NULL
- and v.file# = hc.ktfbhcafno
- and hc.ktfbhctsn = ts.ts#
- and fe.fenum = f.file#;
对于本地管理的表空间,视图显示的文件大小信息来自sys.x$ktfbhc表。
二、文件离线或者表空间离线对查询文件大小的影响
- SQL> alter database datafile 6 offline;
- Database altered.
- SQL> select file#,blocks from file$ where file#=6;
- FILE# BLOCKS
- ---------- ----------
- 6 1280
- SQL> select file#,blocks from v$datafile where file#=6;
- FILE# BLOCKS
- ---------- ----------
- 6 5120
- SQL> select file#,blocks from v$datafile_header where file#=6;
- FILE# BLOCKS
- ---------- ----------
- 6 5120
- SQL> select file_id,blocks from dba_data_files where file_id=6;
- FILE_ID BLOCKS
- ---------- ----------
- 6
- SQL> select KTFBHCAFNO,KTFBHCSZ from sys.x$ktfbhc where KTFBHCAFNO=6;
- KTFBHCAFNO KTFBHCSZ
- ---------- ----------
- 6 1280
文件离线将会导致sys.x$ktfbhc查询的文件大小为创建时候的大小,dba_data_files显示文件大小为NULL,其他视图也不受影响。
- SQL> alter database datafile 6 online;
- alter database datafile 6 online
- *
- ERROR at line 1:
- ORA-01113: file 6 needs media recovery
- ORA-01110: data file 6: '/u01/app/oracle/oradata/huateng/htyansp01.dbf'
- SQL> recover datafile 6;
- Media recovery complete.
- SQL> alter database datafile 6 online;
- Database altered.
- SQL> alter tablespace htyansp online;
- Tablespace altered.
- SQL> alter tablespace htyansp offline;
- Tablespace altered.
- SQL> select file#,blocks from file$ where file#=6;
- FILE# BLOCKS
- ---------- ----------
- 6 1280
- 1 row selected.
- SQL> select file#,blocks from v$datafile where file#=6;
- FILE# BLOCKS
- ---------- ----------
- 6 0
- 1 row selected.
- SQL> select file#,blocks from v$datafile_header where file#=6;
- FILE# BLOCKS
- ---------- ----------
- 6 0
- 1 row selected.
- SQL> select file_id,blocks from dba_data_files where file_id=6;
- FILE_ID BLOCKS
- ---------- ----------
- 6
- 1 row selected.
- SQL> select KTFBHCAFNO,KTFBHCSZ from sys.x$ktfbhc where KTFBHCAFNO=6;
- KTFBHCAFNO KTFBHCSZ
- ---------- ----------
- 6 1280
可以看到表空间离线导致大部分视图查询结果的为0,那么此时我们需要查询文件大小要从哪里查呢?
阅读(1891) | 评论(0) | 转发(0) |