在oracle中，我们可以通过file_id（file#）与block_id（block#）去定位一个数据库对象（object）。例如，我们在10046生成的trace文件中file#=4 block#=266 blocks=8,那么我可以通过下面两个sql去定位对象
sql 1：此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字段）
select objd, file#, block#, class#, ts#, cachehint, status, dirty from v$bh where file# = &file_id and block# = &block_id; select owner, object_name from dba_objects where object_id=&object_id;
下面通过一个例子来演示一下，详情如下所示
sql> 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, 2 segment_name, 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找到了一个最大空闲块。
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的定义：
create or replace view dba_free_space (tablespace_name, file_id, block_id, bytes, 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, fi.file#, 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, fi.file#, 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, fi.file#, 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, file_id, block_id, bytes, 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, fi.file#, 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, fi.file#, 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, fi.file#, 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 /
那么在dba_free_space中找到的最大空闲块是否很有可能就是回收站中曾经的一个对象呢？那么我们来测试看看。
sql> 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,segment_name, 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,file#,block# ,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会变化，而这个变化跟清空回收站是有关系的。如下案例所示：
sql> 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,segment_name, header_file, 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#,owner#,original_name,file#,block# ,flags,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，有兴趣可以验证、测试一下。
以上所述是www.887551.com给大家介绍的关于oracle通过file_id与block_id定位数据库对象遇到的问题引发的思考，希望对大家有所帮助