Oracle数据块解析

关注

转载最新推荐文章于 2026-07-09 16:40:36 发布 337 阅读 AI 写同款· GEO 优化 ›

一. DB(Data block)

　　From: http://www.orafaq.com/wiki/Data_block

 A data block is the smallest unit of storage in an Oracle database. Every database has a default block size (specified when the database is created), although blocks in different tablespaces may have different block sizes.

 An extent consist of one or more contiguous Oracle data blocks. A block determines the finest level of granularity of where data can be stored. One data block corresponds to a specific number of bytes of physical space on disk.

Information about data blocks can be retrieved from the data dictionary views USER_SEGMENTS and USER_EXTENTS. These views show how many blocks are allocated for database object and how many blocks are available(free) in a segment/extent.

　　数据块是oracle中的最小存储单位，每个数据库创建的时候都有一个默认的block size，同时不同的表空间可以有不同的数据块大小。

一个extent是由多个连续的block组成，一个oracle的数据块由操作系统中磁盘中的块来组成。

　　关于data blocks的信息，可以从user_segments和user_extents视图来取得，这些视图可以显示数据库对象分配了多少数据库，并且在segment和extents中有多少是空闲的。

1.1 Dumping data blocks（导出数据块）

SQL> SELECT
  2    dbms_rowid.rowid_relative_fno(rowid) REL_FNO,
  3    dbms_rowid.rowid_block_number(rowid) BLOCKNO,
  4    dbms_rowid.rowid_row_number(rowid) ROWNO,
  5    empno, ename
  6   FROM scott.emp WHERE empno = 7369;

   REL_FNO    BLOCKNO      ROWNO      EMPNO ENAME
---------- ---------- ---------- ---------- ----------
         4         32          0       7369 SMITH

如果想导出多个块，可用

ALTER SYSTEM dump datafile <file_id> block min <block_id> block max <block_id+blocks-1>;

分析导出数据：

SQL> show user
USER is "SYS"
SQL> 
SQL> oradebug setmypid
Statement processed.
SQL> alter system dump datafile 4 block 32;

System altered.

SQL> oradebug tracefile_name
/u01/admin/kxone/udump/kxone_ora_2425.trc

查看文件：/u01/admin/kxone/udump/kxone_ora_2425.trc

   114  tab 0, row 0, @0x1f72
   115  tl: 38 fb: --H-FL-- lb: 0x1  cc: 8
   116  col  0: [ 3]  c2 4a 46
   117  col  1: [ 5]  53 4d 49 54 48
   118  col  2: [ 5]  43 4c 45 52 4b
   119  col  3: [ 3]  c2 50 03
   120  col  4: [ 7]  77 b4 0c 11 01 01 01
   121  col  5: [ 2]  c2 09
   122  col  6: *NULL*
   123  col  7: [ 2]  c1 15

转化成表中数据：

SQL> SELECT utl_raw.cast_to_number(replace('c2 4a 46',' ')) value FROM dual;

     VALUE
----------
      7369

SQL> SELECT utl_raw.cast_to_varchar2(replace('53 4d 49 54 48',' ')) value FROM dual;

VALUE
--------------------------------------------------------------------------------
SMITH

二. DBA（Data Block Address）数据块地址

From：http://www.orafaq.com/wiki/Data_block_address

A Data Block Address (DBA) is the address of an Oracle data block for access purposes.

DBA一般指绝对数据块地址. rowid用来表示一行的物理地址，一行唯一确定一个rowid，并且在使用中一般不会改变，除非rowid之后在行的物理位置发生改变的情况下才会发生变化。在rowid 中，就有一段是来表示DBA的。有关rowid的内容，参考我的Blog：

Oracle Rowid 介绍

http://blog.csdn.net/tianlesoftware/archive/2009/12/16/5020718.aspx

2.1 Find the DBA for a given row，用给定的行取得dba

SQL> SELECT
  2       dbms_rowid.rowid_relative_fno(rowid) REL_FNO,
  3       dbms_rowid.rowid_block_number(rowid) BLOCKNO,
  4       empno, ename
  5      FROM scott.emp WHERE empno = 7521;

   REL_FNO    BLOCKNO      EMPNO ENAME
---------- ---------- ---------- ----------
         4         32       7521 WARD

SQL> variable dba varchar2(30)
SQL> exec :dba := dbms_utility.make_data_block_address(4, 32);

PL/SQL procedure successfully completed.

SQL> 
SQL> print dba

DBA
--------------------------------------------------------------------------------
16777248

2.2 将dba地址转换成数据文件和行号

SQL> SELECT dbms_utility.data_block_address_block(16777248) "BLOCK",
  2         dbms_utility.data_block_address_file(16777248) "FILE"
  3       FROM dual;

     BLOCK       FILE
---------- ----------
        32          4

三. RDBA（Tablespace relative database block address）

在讲RDBA 之前，要先了解下rowid的组成。关于rowid的内容，参考我的blog：

Oracle Rowid 介绍

http://blog.csdn.net/tianlesoftware/archive/2009/12/16/5020718.aspx

RDBA是相对数据块地址，是数据字典(表空间及一些对象定义)所在块的地址。

oracle 8以后，rowid的存储空间扩大到了10个字节(32bit object#+10bit rfile#+22bit block#+16bit row#)。rdba就是rowid中的rfile#+block#

SQL> SELECT
  2  rowid,
  3  dbms_rowid.rowid_relative_fno(rowid) REL_FNO,
  4  dbms_rowid.rowid_block_number(rowid) BLOCKNO,
  5  dbms_rowid.rowid_row_number(rowid) ROWNO,
  6  empno, ename
  7  FROM scott.emp WHERE empno = 7521;

ROWID                 REL_FNO    BLOCKNO      ROWNO      EMPNO ENAME
------------------ ---------- ---------- ---------- ---------- ----------
AAAMl3AAEAAAAAgAAC          4         32          2       7521 WARD

把这个block dump到trace:

SYS@anqing1(rac1)> alter system dump datafile 4 block 32;

System altered.

查看当前的trace 文件位置：

SQL> oradebug setmypid;

Statement processed.

SQL> oradebug tracefile_name

/u01/admin/kxone/udump/kxone_ora_2425.trc

[oracle@oraclenode01 udump]$ nl /u01/admin/kxone/udump/kxone_ora_2425.trc

14  *** 2013-04-23 15:00:17.485
    15  *** ACTION NAME:() 2013-04-23 15:00:17.485
    16  *** MODULE NAME:(sqlplus@oraclenode01 (TNS V1-V3)) 2013-04-23 15:00:17.485
    17  *** SERVICE NAME:(SYS$USERS) 2013-04-23 15:00:17.485
    18  *** SESSION ID:(235.8187) 2013-04-23 15:00:17.485
    19  Start dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32
    20  buffer tsn: 4 rdba: 0x01000020 (4/32)   -- rdba 的值
    21  scn: 0x0000.0005deed seq: 0x01 flg: 0x06 tail: 0xdeed0601
    22  frmt: 0x02 chkval: 0x7cd7 type: 0x06=trans data
    23  Hex dump of block: st=0, typ_found=1
    24  Dump of memory from 0x000000001EC92800 to 0x000000001EC94800

   244  tab 0, row 13, @0x1d61
   245  tl: 39 fb: --H-FL-- lb: 0x1  cc: 8
   246  col  0: [ 3]  c2 50 23
   247  col  1: [ 6]  4d 49 4c 4c 45 52
   248  col  2: [ 5]  43 4c 45 52 4b
   249  col  3: [ 3]  c2 4e 53
   250  col  4: [ 7]  77 b6 01 17 01 01 01
   251  col  5: [ 2]  c2 0e
   252  col  6: *NULL*
   253  col  7: [ 2]  c1 0b
   254  end_of_block_dump
   255  End dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32

其中：

rdba: 0x01000020 (4/32)

SQL> /* Formatted on 2011/6/7 11:27:10 (QP5 v5.163.1008.3004) */
SQL> SELECT DBMS_UTILITY.data_block_address_file (
  2            TO_NUMBER (LTRIM ('0x01000020', '0x'), 'xxxxxxxx'))
  3            AS file_no,
  4         DBMS_UTILITY.data_block_address_block (
  5            TO_NUMBER (LTRIM ('0x01000020', '0x'), 'xxxxxxxx'))
  6            AS block_no
  7    FROM DUAL;

   FILE_NO   BLOCK_NO
---------- ----------
         4         32

刚才说了，在32这个块里保存了14条row记录，我们继续查询一下我们where=7521 那条：

SQL> select DBMS_UTILITY.data_block_address_block (TO_NUMBER (LTRIM ('0x1d61', '0x'),'xxxxxxxx')) as block_no from dual;

  BLOCK_NO
----------
      7521

SQL>