个人中心
gpt4 book ai didi

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

转载 作者：qq735679552 更新时间：2022-09-29 22:32:09 25 4
gpt4 key购买 nike

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章关于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效率较差，执行时间较长.

?
1
2
3
4
5
6
7
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字段） 。

?
1
2
3
4
5
6
7
8
9
10
11
12
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;

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
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

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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通过file_id与block_id定位数据库对象遇到的问题引发的思考

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

ORACLE 10g中DBA_FREE_SPACE的定义:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
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中找到的最大空闲块是否很有可能就是回收站中曾经的一个对象呢？那么我们来测试看看.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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

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

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
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

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

如上所示，在清空回收站的表以后，你查询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定位数据库对象遇到的问题引发的思考，希望对大家有所帮助，如果大家有任何疑问欢迎给我留言，小编会及时回复大家的！ 。

原文链接：http://www.cnblogs.com/kerrycode/p/6576988.html 。

最后此篇关于关于ORACLE通过file_id与block_id定位数据库对象遇到的问题引发的思考的文章就讲到这里了,如果你想了解更多关于关于ORACLE通过file_id与block_id定位数据库对象遇到的问题引发的思考的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。

25 4 0
文章推荐： 查询表中某字段有重复记录个数的方法
文章推荐： 分享：在存储过程中使用另一个存储过程返回的查询结果集的方法
文章推荐： Oracle Translate 统计字符出现的次数示例代码
文章推荐： ORACLE 11g安装中出现xhost: unable to open display问题解决步骤
qq735679552
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com