關于ORACLE通過file_id與block_id定位數據庫對象遇到的問題引發的思考

2024-08-29 13:59:52

字體：大中小

來源：轉載

供稿：網友

在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   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

oracle,定位數據庫對象

昨天在群里討論一個關于空閑塊的問題時，我驗證測試時，發現一個奇怪的現象，使用下面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 ) 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,定位數據庫對象

后面查了一下資料，發現在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)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中找到的最大空閑塊是否很有可能就是回收站中曾經的一個對象呢？那么我們來測試看看。

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

oracle,定位數據庫對象

如上所示，清空回收站對象后，你會發現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  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

oracle,定位數據庫對象