今天在测试库检查数据文件大小的时候,发现FILE$记录的与数据字典视图不一致,本以为file$记录的就是文件的大小,实际却不是这样的。
- SQL> select file#,blocks from v$datafile_header;
- FILE# BLOCKS
- ---------- ----------
- 1 88320
- 2 62720
- 3 10880
- 4 640
- 5 12800
- 6 5120
- 6 rows selected.
- SQL> select file_id,blocks from dba_data_files order by 1;
- FILE_ID BLOCKS
- ---------- ----------
- 1 88320
- 2 62720
- 3 10880
- 4 640
- 5 12800
- 6 5120
- 6 rows selected.
- SQL> select file#,blocks from file$;
- FILE# BLOCKS
- ---------- ----------
- 1 64000
- 2 51200
- 3 3200
- 4 640
- 5 12800
- 6 1280
- 6 rows selected.
上面的结果显示dba_data_files与v$datafile_header一样,也与实际文件的大小一样,而file$记录的却与实际文件大小不一样。
通过创建DBA_DATA_FILES视图脚本发现这个视图来自2部分,一个是file$.spare1 is NULL的情况,一个是file$.spare1 is not NULL情况。
如下所示:
- 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#;
对于spare1 is NULL的情况,DBA_DATA_FILES显示的文件BLOCKS来自file$视图,而对于spare1 is NOT NULL的情况,
实际文件的大小来自sys.x$ktfbhc视图。
查询了一下这个FILE$表的创建脚本,SPARE1列的含义是tablespace-relative DBA of space file header。
- create table file$ /* file table */
- ( file# number not null, /* file identifier number */
- status$ number not null, /* status (see KTS.H): */
- /* 1 = INVALID, 2 = AVAILABLE */
- blocks number not null, /* size of file in blocks */
- /* zero for bitmapped tablespaces */
- ts# number, /* tablespace that owns file */
- relfile# number, /* relative file number */
- maxextend number, /* maximum file size */
- inc number, /* increment amount */
- crscnwrp number, /* creation SCN wrap */
- crscnbas number, /* creation SCN base */
- ownerinstance varchar("M_IDEN"), /* Owner instance name */
- spare1 number, /* tablespace-relative DBA of space file header */
- /* NULL for dictionary-mapped tablespaces */
- spare2 number,
- spare3 varchar2(1000),
- spare4 date
- )
- /
查询表file$ 的spare1列确实都不是为NULL的,如下:
- 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
- 6 rows selected.
DBA_DATA_FILES显示的正确结果实际来自表sys.x$ktfbhc,这个表的含义是Kernel Tablespace File Bitmap Header Control
- SQL> select KTFBHCAFNO,KTFBHCSZ from sys.x$ktfbhc;
- KTFBHCAFNO KTFBHCSZ
- ---------- ----------
- 1 88320
- 2 62720
- 3 10880
- 4 640
- 5 12800
- 6 5120
- 6 rows selected.
实际视图的这2部分分别代表字典管理的表空间和本地管理的表空间。
对于本地管理的表空间,file$表记录的是文件创建时候的大小,一旦文件以后自动扩展了或者RESIZLE了,file$
表显示的文件大小必须从sys.x$ktfbhc查询了。
如下:
- SQL> select 1000*8192 from dual;
- 1000*8192
- ----------
- 8192000
- 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.
因此正确的查询文件的大小还是通过DBA_DATA_FILES保险,无论是本地管理的表空间还是字典管理的表空间。
file$表显示的大小对于本地管理的表空间是不正确的。
阅读(2174) | 评论(0) | 转发(0) |