在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 266SQL> 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 USERSSQL> 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 NSQL> SELECT OWNER, OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_ID=76090;OWNER OBJECT_NAME------------ ------------------------------------------------------------TEST EMPLOYEEclip_image001 |
昨天在群里讨论一个关于空闲块的问题时，我验证测试时，发现一个奇怪的现象，使用下面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 ) DWHERE 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的定义：
|
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)asselect ts.name, fi.file#, f.block#, f.length * ts.blocksize, f.length, f.file#from sys.ts$ ts, sys.fet$ f, sys.file$ fiwhere ts.ts# = f.ts# and f.ts# = fi.ts# and f.file# = fi.relfile# and ts.bitmapped = 0union allselect /*+ ordered use_nl(f) use_nl(fi) */ ts.name, fi.file#, f.ktfbfebno, f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefnofrom sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fiwhere 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$ = 0union allselect /*+ ordered use_nl(u) use_nl(fi) */ ts.name, fi.file#, u.ktfbuebno, u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefnofrom sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fiwhere 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$ = 0union allselect 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$ rbwhere 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)asselect ts.name, fi.file#, f.block#, f.length * ts.blocksize, f.length, f.file#from sys.ts$ ts, sys.fet$ f, sys.file$ fiwhere ts.ts# = f.ts# and f.ts# = fi.ts# and f.file# = fi.relfile# and ts.bitmapped = 0union allselect /*+ ordered use_nl(f) use_nl(fi) */ ts.name, fi.file#, f.ktfbfebno, f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefnofrom sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fiwhere 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$ = 0union allselect /*+ ordered use_nl(u) use_nl(fi) */ ts.name, fi.file#, u.ktfbuebno, u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefnofrom sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fiwhere 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$ = 0union allselect 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$ rbwhere 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 onSQL> 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 113025SQL> SQL> SELECT * FROM X$KTFBFE WHERE KTFBFEFNO=97;ADDR INDX INST_ID KTFBFETSN KTFBFEFNO KTFBFEBNO KTFBFEBLKS---------------- ---------- ---------- ---------- ---------- ---------- ----------00007F57B2388CA0 222 1 9 97 524169 120SQL> 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 896SQL> 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 800007F57B2388CA0 225 1 9 97 524169 120SQL> clip_image003 |
如上所示，清空回收站对象后，你会发现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 onSQL> 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 130SQL> SELECT * FROM X$KTFBFE WHERE KTFBFEFNO=5 ;ADDR INDX INST_ID KTFBFETSN KTFBFEFNO KTFBFEBNO KTFBFEBLKS---------------- ---------- ---------- ---------- ---------- ---------- ----------00002BA829B19558 150 1 6 5 1280 50675200002BA829B19558 151 1 6 5 508032 16256SQL> 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 1152SQL> SELECT * FROM X$KTFBFE WHERE KTFBFEFNO=5 ;ADDR INDX INST_ID KTFBFETSN KTFBFEFNO KTFBFEBNO KTFBFEBLKS---------------- ---------- ---------- ---------- ---------- ---------- ----------00002BA829B159D8 150 1 6 5 1280 50675200002BA829B159D8 151 1 6 5 508032 16256SQL> 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 50790400002BA829B159D8 151 1 6 5 508032 16256SQL> 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定位数据库对象遇到的问题引发的思考，希望对大家有所帮助，如果大家有任何疑问欢迎给我留言，小编会及时回复大家的！
原文链接：http://www.cnblogs.com/kerrycode/p/6576988.html
