在ORACLE中，我們可以通過(guò)file_id（file#）與block_id（block#）去定位一個(gè)數(shù)據(jù)庫(kù)對(duì)象（object）。例如，我們?cè)?0046生成的trace文件中file#=4 block#=266 blocks=8,那么我可以通過(guò)下面兩個(gè)SQL去定位對(duì)象
SQL 1：此SQL效率較差，執(zhí)行時(shí)間較長(zhǎng)。
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 中沒(méi)有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;
下面通過(guò)一個(gè)例子來(lái)演示一下，詳情如下所示
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
昨天在群里討論一個(gè)關(guān)于空閑塊的問(wèn)題時(shí)，我驗(yàn)證測(cè)試時(shí)，發(fā)現(xiàn)一個(gè)奇怪的現(xiàn)象，使用下面SQL找到了一個(gè)最大空閑塊。
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;
然后我發(fā)現(xiàn)使用上面兩個(gè)SQL查不到對(duì)應(yīng)的對(duì)象。如下截圖所示：
后面查了一下資料，發(fā)現(xiàn)在Oracle Database 10g引入了回收站功能后，會(huì)將回收站（RECYCLEBIN$）中的空間計(jì)算為自由空間，加入到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中找到的最大空閑塊是否很有可能就是回收站中曾經(jīng)的一個(gè)對(duì)象呢？那么我們來(lái)測(cè)試看看。
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
如上所示，清空回收站對(duì)象后，你會(huì)發(fā)現(xiàn)X$KTFBFE中多了一條記錄，KTFBFEFNO 和 KTFBFEBNO分別為97 ，113025， 這個(gè)值顯然就是刪除對(duì)象TTT曾經(jīng)的FILE_ID（97）和BLOCK_ID（113025）值。
另外，在測(cè)試過(guò)程中發(fā)現(xiàn)，并不是每次的測(cè)試結(jié)果都是在X$KTFBFE中多一條記錄，有時(shí)候記錄不會(huì)變化，但是X$KTFBFE中某條記錄的KTFBFEBNO會(huì)變化，而這個(gè)變化跟清空回收站是有關(guān)系的。如下案例所示：
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，就會(huì)發(fā)現(xiàn)其中一條記錄的KTFBFEBNO的變化了，它們的關(guān)系為
1280 -1152 = 128
所以，你會(huì)看到KTFBFEBNO的值從1280變?yōu)榱?28了。此時(shí)你查看DBA_FREE_SPACE，就會(huì)看到這樣的情況。所以當(dāng)清空回收站時(shí)，有可能是數(shù)據(jù)庫(kù)將這個(gè)表的空間標(biāo)記為了空閑塊，也有可能是將這個(gè)空閑塊合并到其它空閑塊去了。
X$KTFBFE其實(shí)是這幾個(gè)單詞[k]ernel [t]ablespace [f]ile [b]itmapped [f]ree [e]xtents 的首字母。關(guān)于這個(gè)系統(tǒng)視圖最深入的介紹，莫過(guò)于這篇文章談?wù)凮racle dba_free_space，有興趣可以驗(yàn)證、測(cè)試一下。
以上所述是小編給大家介紹的關(guān)于ORACLE通過(guò)file_id與block_id定位數(shù)據(jù)庫(kù)對(duì)象遇到的問(wèn)題引發(fā)的思考，希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)歡迎給我留言，小編會(huì)及時(shí)回復(fù)大家的！
標(biāo)簽：百色 中衛(wèi) 海東 自貢 紹興 寧波 昭通 遼源
巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《關(guān)于ORACLE通過(guò)file_id與block_id定位數(shù)據(jù)庫(kù)對(duì)象遇到的問(wèn)題引發(fā)的思考》，本文關(guān)鍵詞 關(guān)于,ORACLE,通過(guò),file,與,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。
