服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Oracle - 关于ORACLE通过file_id与block_id定位数据库对象遇到的问题引发的思考

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

2020-01-07 15:33潇湘隐者 Oracle

这篇文章主要介绍了关于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

    • 延伸 · 阅读

    精彩推荐
    最近更新
    编辑推荐
    阅读排行
    热门标签
    1033 　 1455 　 12154 　 10231 　 12514 　 54013 　 数据库系统 　 快照 　 分区策略 　 数据库集 　 异常关闭 　 Oracle9iPL 　 SQL编程 　 日期函数 　 SQL性能优化 　 并行查询 　 紧急故障 　 完全卸载 　 Win2k 　 IMP 　 分区管理 　 密码文件 　 AutoUpgrade 　 异地备份 　 交叉表 　 加载数据库错误 　 areasQueries 　 逻辑备份 　 SH文件 　 数据文件 　
    © 2019-2023 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图