关于ORACLE通过file_id与block_id定位数据库对象遇到的问题引发的思考

前端之家收集整理的这篇文章主要介绍了关于ORACLE通过file_id与block_id定位数据库对象遇到的问题引发的思考前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

在ORACLE中,我们可以通过file_id(file#)与block_id(block#)去定位一个数据库对象(object)。例如,我们在10046生成的trace文件中file#=4 block#=266 blocks=8,那么我可以通过下面两个sql去定位对象

sql 1:此sql效率较差,执行时间较长。

sql;"> SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME FROM DBA_EXTENTS WHERE FILE_ID =&FILE_ID AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;

sql 2:此sql效率较快(ORACLE 10g 中没有CACHEHINT字段)

下面通过一个例子来演示一下,详情如下所示

COL OWNER FOR A12; sql> COL SEGMENT_NAME FOR A32; sql> SELECT OWNER,2 SEGMENT_NAME,3 HEADER_FILE,4 HEADER_BLOCK 5 FROM DBA_SEGMENTS 6 WHERE OWNER='TEST' AND SEGMENT_NAME='EMPLOYEE'; OWNER SEGMENT_NAME HEADER_FILE HEADER_BLOCK ------------ -------------------------------- ----------- ------------ TEST EMPLOYEE 4 266 sql> sql> SELECT OWNER,3 SEGMENT_TYPE,4 TABLESPACE_NAME 5 FROM DBA_EXTENTS 6 WHERE FILE_ID = 4 7 AND 266 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1; OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME ------------ -------------------------------- ------------------ ----------------- TEST EMPLOYEE TABLE USERS sql> sql> SELECT OBJD,2 FILE#,3 BLOCK#,4 CLASS#,5 TS#,6 CACHEHINT,7 STATUS,8 DIRTY 9 FROM V$BH 10 WHERE FILE# = 4 11 AND BLOCK# = 266; OBJD FILE# BLOCK# CLASS# TS# CACHEHINT STATUS D ---------- ---------- ---------- ---------- ---------- ---------- ---------- - 76090 4 266 4 4 15 cr N 76090 4 266 4 4 15 cr N 76090 4 266 4 4 15 cr N sql> SELECT OWNER,OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_ID=76090; OWNER OBJECT_NAME ------------ ------------------------------------------------------------ TEST EMPLOYEE clip_image001

昨天在群里讨论一个关于空闲块的问题时,我验证测试时,发现一个奇怪的现象,使用下面sql找到了一个最大空闲块。

sql;"> SELECT UPPER(F.TABLESPACE_NAME) AS "表空间名",D.TOT_GROOTTE_MB AS "表空间大小(M)",D.TOT_GROOTTE_MB - F.TOTAL_BYTES AS "已使用空间(M)",TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') AS "使用比",F.TOTAL_BYTES AS "空闲空间(M)",F.MAX_BYTES AS "最大空闲块(M)" FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024),2) TOTAL_BYTES,ROUND(MAX(BYTES) / (1024 * 1024),2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F,(SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 * 1024),2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME ) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME; SELECT FILE_ID,BLOCK_ID,BYTES,BLOCKS FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME=&TABLESPACE_NAME ORDER BY BYTES DESC;

然后我发现使用上面两个sql查不到对应的对象。如下截图所示:

后面查了一下资料,发现在Oracle Database 10g引入了回收站功能后,会将回收站(RECYCLEBIN$)中的空间计算为自由空间,加入到dba_free_space字典中。在$ORACLE_HOME/rdbms/admin/catspace.sql中,你可以找到视图DBA_FREE_SPACE的定义,脚本如下:

ORACLE 10g中DBA_FREE_SPACE的定义:

sql;"> create or replace view DBA_FREE_SPACE (TABLESPACE_NAME,FILE_ID,BLOCKS,RELATIVE_FNO) as select ts.name,fi.file#,f.block#,f.length * ts.blocksize,f.length,f.file# from sys.ts$ ts,sys.fet$ f,sys.file$ fi where ts.ts# = f.ts# and f.ts# = fi.ts# and f.file# = fi.relfile# and ts.bitmapped = 0 union all select /*+ ordered use_nl(f) use_nl(fi) */ ts.name,f.ktfbfebno,f.ktfbfeblks * ts.blocksize,f.ktfbfeblks,f.ktfbfefno from sys.ts$ ts,sys.x$ktfbfe f,sys.file$ fi where ts.ts# = f.ktfbfetsn and f.ktfbfetsn = fi.ts# and f.ktfbfefno = fi.relfile# and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0 union all select /*+ ordered use_nl(u) use_nl(fi) */ ts.name,u.ktfbuebno,u.ktfbueblks * ts.blocksize,u.ktfbueblks,u.ktfbuefno from sys.recyclebin$ rb,sys.ts$ ts,sys.x$ktfbue u,sys.file$ fi where ts.ts# = rb.ts# and rb.ts# = fi.ts# and u.ktfbuefno = fi.relfile# and u.ktfbuesegtsn = rb.ts# and u.ktfbuesegfno = rb.file# and u.ktfbuesegbno = rb.block# and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0 union all select ts.name,u.block#,u.length * ts.blocksize,u.length,u.file# from sys.ts$ ts,sys.uet$ u,sys.file$ fi,sys.recyclebin$ rb where ts.ts# = u.ts# and u.ts# = fi.ts# and u.segfile# = fi.relfile# and u.ts# = rb.ts# and u.segfile# = rb.file# and u.segblock# = rb.block# and ts.bitmapped = 0 / ORACLE 11g中DBA_FREE_SPACE的定义: create or replace view DBA_FREE_SPACE (TABLESPACE_NAME,sys.recyclebin$ rb where ts.ts# = u.ts# and u.ts# = fi.ts# and u.segfile# = fi.relfile# and u.ts# = rb.ts# and u.segfile# = rb.file# and u.segblock# = rb.block# and ts.bitmapped = 0 /

那么在DBA_FREE_SPACE中找到的最大空闲块是否很有可能就是回收站中曾经的一个对象呢?那么我们来测试看看。

show parameter recyclebin; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ recyclebin string on sql> CREATE TABLE ESCMOWNER.TTT 2 AS 3 SELECT * FROM DBA_OBJECTS; Table created. sql> COL OWNER FOR A12; sql> COL SEGMENT_NAME FOR A32; sql> SELECT OWNER,HEADER_FILE,HEADER_BLOCK 2 FROM DBA_SEGMENTS 3 WHERE OWNER='ESCMOWNER' AND SEGMENT_NAME='TTT' ; OWNER SEGMENT_NAME HEADER_FILE HEADER_BLOCK ------------ -------------------------------- ----------- ------------ ESCMOWNER TTT 97 113025 sql> sql> SELECT * FROM X$KTFBFE WHERE KTFBFEFNO=97; ADDR INDX INST_ID KTFBFETSN KTFBFEFNO KTFBFEBNO KTFBFEBLKS ---------------- ---------- ---------- ---------- ---------- ---------- ---------- 00007F57B2388CA0 222 1 9 97 524169 120 sql> DROP TABLE ESCMOWNER.TTT; Table dropped. sql> COL ORIGINAL_NAME FOR A16; sql> SELECT OBJ#,OWNER#,ORIGINAL_NAME,FLAGS,SPACE FROM RECYCLEBIN$; OBJ# OWNER# ORIGINAL_NAME FILE# BLOCK# FLAGS SPACE ---------- ---------- ---------------- ---------- ---------- ---------- ---------- 805429 73 TTT 97 113025 30 896 sql> PURGE DBA_RECYCLEBIN; DBA Recyclebin purged. sql> SELECT * FROM X$KTFBFE WHERE KTFBFEFNO=97 ; ADDR INDX INST_ID KTFBFETSN KTFBFEFNO KTFBFEBNO KTFBFEBLKS ---------------- ---------- ---------- ---------- ---------- ---------- ---------- 00007F57B2388CA0 222 1 9 97 113025 8 00007F57B2388CA0 225 1 9 97 524169 120 sql> clip_image003

如上所示,清空回收站对象后,你会发现X$KTFBFE中多了一条记录,KTFBFEFNO 和 KTFBFEBNO分别为97 ,113025, 这个值显然就是删除对象TTT曾经的FILE_ID(97)和BLOCK_ID(113025)值。

另外,在测试过程中发现,并不是每次的测试结果都是在X$KTFBFE中多一条记录,有时候记录不会变化,但是X$KTFBFE中某条记录的KTFBFEBNO会变化,而这个变化跟清空回收站是有关系的。如下案例所示:

show parameter recyclebin; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ recyclebin string on sql> CREATE TABLE TEST.TTT 2 AS 3 SELECT * FROM DBA_OBJECTS; Table created. sql> COL OWNER FOR A12; sql> COL SEGMENT_NAME FOR A32; sql> SELECT OWNER,HEADER_BLOCK 2 FROM DBA_SEGMENTS 3 WHERE OWNER='TEST' AND SEGMENT_NAME='TTT' ; OWNER SEGMENT_NAME HEADER_FILE HEADER_BLOCK ------------ -------------------------------- ----------- ------------ TEST TTT 5 130 sql> SELECT * FROM X$KTFBFE WHERE KTFBFEFNO=5 ; ADDR INDX INST_ID KTFBFETSN KTFBFEFNO KTFBFEBNO KTFBFEBLKS ---------------- ---------- ---------- ---------- ---------- ---------- ---------- 00002BA829B19558 150 1 6 5 1280 506752 00002BA829B19558 151 1 6 5 508032 16256 sql> DROP TABLE TEST.TTT; Table dropped. sql> sql> COL ORIGINAL_NAME FOR A16; sql> SELECT OBJ#,SPACE FROM RECYCLEBIN$; OBJ# OWNER# ORIGINAL_NAME FILE# BLOCK# FLAGS SPACE ---------- ---------- ---------------- ---------- ---------- ---------- ---------- 82820 85 TTT 5 130 30 1152 sql> SELECT * FROM X$KTFBFE WHERE KTFBFEFNO=5 ; ADDR INDX INST_ID KTFBFETSN KTFBFEFNO KTFBFEBNO KTFBFEBLKS ---------------- ---------- ---------- ---------- ---------- ---------- ---------- 00002BA829B159D8 150 1 6 5 1280 506752 00002BA829B159D8 151 1 6 5 508032 16256 sql> PURGE DBA_RECYCLEBIN; DBA Recyclebin purged. sql> SELECT * FROM X$KTFBFE WHERE KTFBFEFNO=5 ; ADDR INDX INST_ID KTFBFETSN KTFBFEFNO KTFBFEBNO KTFBFEBLKS ---------------- ---------- ---------- ---------- ---------- ---------- ---------- 00002BA829B159D8 150 1 6 5 128 507904 00002BA829B159D8 151 1 6 5 508032 16256 sql> clip_image004

如上所示,在清空回收站的表以后,你查询X$KTFBFE,就会发现其中一条记录的KTFBFEBNO的变化了,它们的关系为

1280 -1152 = 128

所以,你会看到KTFBFEBNO的值从1280变为了128了。此时你查看DBA_FREE_SPACE,就会看到这样的情况。所以当清空回收站时,有可能是数据库将这个表的空间标记为了空闲块,也有可能是将这个空闲块合并到其它空闲块去了。

X$KTFBFE其实是这几个单词[k]ernel [t]ablespace [f]ile [b]itmapped [f]ree [e]xtents 的首字母。关于这个系统视图最深入的介绍,莫过于这篇文章谈谈Oracle dba_free_space,有兴趣可以验证、测试一下。

以上所述是小编给大家介绍的关于ORACLE通过file_id与block_id定位数据库对象遇到的问题引发的思考,希望对大家有所帮助,如果大家有任何疑问欢迎给我留言,小编会及时回复大家的!

原文链接:https://www.f2er.com/oracle/64926.html

猜你在找的Oracle相关文章