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

作者：潇湘隐者字体：[增加减小] 来源：互联网时间：2017-05-11

潇湘隐者通过本文主要向大家介绍了关于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效率较差，执行时间较长。

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;

</div>

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;

</div>

下面通过一个例子来演示一下，详情如下所示

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

</div>

昨天在群里讨论一个关于空闲块的问题时，我验证测试时，发现一个奇怪的现象，使用下面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;

</div>

然后我发现使用上面两个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
/

</div>

那么在DBA_FREE_SPACE中找到的最大空闲块是否很有可能就是回收站中曾经的一个对象呢？那么我们来测试看看。

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 S

分享到：QQ空间新浪微博腾讯微博微信百度贴吧 QQ好友复制网址打印

您可能想查找下面的文章:

2017-05-11浅析Oracle中的不等于号
2017-05-11解析oracle对select加锁的方法以及锁的查询
2017-05-11oracle误drop/update操作后的数据恢复测试
2017-05-11在Oracle网络结构解决连接问题
2017-05-11oracle中distinct的用法详解
2017-05-11CentOS 6.3下安装部署Oracle服务器图文教程
2017-08-17初学oracle体系结构
2017-05-11oracle报错（ORA-00600）问题处理
2017-05-11delete archivelog all无法清除归档日志解决方法
2017-05-11oracle 查询当天数据的sql条件写法

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

您可能想查找下面的文章:

相关文章

文章分类

最近更新的内容