主页 > 数据库 > id定位数据库对象遇到的问题引发的思考

id定位数据库对象遇到的问题引发的思考

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

昨天在群里讨论一个关于空闲块的问题时,我验证测试时,发现一个奇怪的现象,使用下面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 ) 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 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) 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中找到的最大空闲块是否很有可能就是回收站中曾经的一个对象呢?那么我们来测试看看。

说点什么吧
  • 全部评论(0
    还没有评论,快来抢沙发吧!