oracle db、dba和rdba

一、     DB（Data Block）

1、dumping data blocks

SYS@ tsid > 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  id,name

  6  from t

  7  where id=1;

   REL_FNO    BLOCKNO      ROWNO   ID   NAME

---------- ---------- ---------- ---------- --------  -----------  -----------

         4         15          0       1   a

SYS@ tsid > alter system dump datafile 4 block 15;

System altered.

dump檔案部分：

block_row_dump:

tab 0, row 0, @0x1f90

tl: 8 fb: --H-FL-- lb: 0x1  cc: 2

col  0: [ 2]  c1 02

col  1: [ 1]  61

end_of_block_dump

End dump data blocks tsn: 4 file#: 4 minblk 15 maxblk 15

2、由dump出來的值轉換為表中的值

SYS@ tsid > select utl_raw.cast_to_number(replace('c1,02',',')) value from dual;

     VALUE

----------

         1

SYS@ tsid > select utl_raw.cast_to_varchar2('61') value from dual;

VALUE

------------------------------------------------------------------

a

二、     DBA（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的。

1、  獲得一行的file number和block number

SYS@ tsid > select

  2  dbms_rowid.rowid_relative_fno(rowid) REL_FNO,

  3  dbms_rowid.rowid_block_number(rowid) BLOCKNO,

  4  id,name

  5  from t where id=2;

   REL_FNO    BLOCKNO      ID    NAME

---------- ---------- ---------- -------- ----------- ------------

         4         12        2   b

2、將file number和block number轉換成DBA

SYS@ tsid > variable dba varchar2(30)

SYS@ tsid > exec :dba :=dbms_utility.make_data_block_address(4,12);

PL/SQL procedure successfully completed.

SYS@ tsid > print dba

DBA

--------------------------------

16777228

3、將DBA轉換成file number和block number

SYS@ tsid > select

  2  dbms_utility.data_block_address_block(16777228) "BLOCK",

  3  dbms_utility.data_block_address_file(16777228) "FILE"

  4  from dual;

     BLOCK       FILE

---------- ----------

        12          4

三、     RDBA（Tablespace relative database block address）

RDBA是相對資料塊地址，是資料字典(表空間及一些物件定義)所在塊的地址。

oracle 8以後，rowid的儲存空間擴大到了10個位元組(32bit object#+10bit rfile#+22bit block#+16bit row#)。rdba就是rowid中的rfile#+block#。

SYS@ tsid > select rowid,

  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  id,name

  6  from t

  7  where id=3;

ROWID                 REL_FNO    BLOCKNO      ROWNO     ID  NAME

------------------            ---------------   --------------     ----------  ---------- --------

AAADJrAAEAAAAAMAAB          4         12          1       3   c

把這個block dump到trace：

SYS@ tsid > alter system dump datafile 4 block 12;

System altered.

檢視dump內容：

=============================================================================

*** 2012-05-15 21:48:40.987

Start dump data blocks tsn: 4 file#: 4 minblk 12 maxblk 12

buffer tsn: 4 rdba: 0x0100000c (4/12)  --rdba的值

scn: 0x0000.002f1254 seq: 0x01 flg: 0x06 tail: 0x12540601

frmt: 0x02 chkval: 0xe59f type: 0x06=trans data

Hex dump of block: st=0, typ_found=1

Dump of memory from 0x07357800 to 0x07359800

7357800 0000A206 0100000C 002F1254 06010000  [........T./.....]

7357810 0000E59F 00000001 0000326B 002EA961  [........k2..a...]

7357820 00000000 00320002 01000009 00220002  [......2.......".]

7357830 0000034D 00800A91 002800F2 00002002  [M.........(.. ..]

7357840 002F1254 00000000 00000000 00000000  [T./.............]

7357850 00000000 00000000 00000000 00000000  [................]

7357860 00000000 00020100 0016FFFF 1F701F88  [..............p.]

7357870 00001F70 1F900002 00001F88 00000000  [p...............]

7357880 00000000 00000000 00000000 00000000  [................]

        Repeat 501 times

73597E0 00000000 00000000 00000000 0202012C  [............,...]

73597F0 630104C1 0202012C 620103C1 12540601  [...c,......b..T.]

Block header dump:  0x0100000c

 Object id on Block? Y

 seg/obj: 0x326b  csc: 0x00.2ea961  itc: 2  flg: E  typ: 1 - DATA

     brn: 0  bdba: 0x1000009 ver: 0x01 opc: 0

     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0002.022.0000034d  0x00800a91.00f2.28  --U-    2  fsc 0x0000.002f1254

0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

data_block_dump,data header at 0x7357864

===============

tsiz: 0x1f98

hsiz: 0x16

pbl: 0x07357864

bdba: 0x0100000c

     76543210

flag=--------

ntab=1

nrow=2

frre=-1

fsbo=0x16

fseo=0x1f88

avsp=0x1f70

tosp=0x1f70

0xe:pti[0] nrow=2    offs=0  --該塊中儲存了2條記錄。從row0到row1

0x12:pri[0]        offs=0x1f90

0x14:pri[1]        offs=0x1f88

block_row_dump:

tab 0, row 0, @0x1f90

tl: 8 fb: --H-FL-- lb: 0x1  cc: 2

col  0: [ 2]  c1 03

col  1: [ 1]  62

tab 0, row 1, @0x1f88

tl: 8 fb: --H-FL-- lb: 0x1  cc: 2

col  0: [ 2]  c1 04

col  1: [ 1]  63

end_of_block_dump

End dump data blocks tsn: 4 file#: 4 minblk 12 maxblk 12

SYS@tsid>select dbms_utility.data_block_address_file(to_number(ltrim('0x0100000c','0x'),'xxxxxxxx')) as file_no,

  2    dbms_utility.data_block_address_block(to_number(ltrim('0x0100000c','0x'),'xxxxxxxx')) as block_no

  3     from dual;

   FILE_NO   BLOCK_NO

---------- ----------

         4         12

這和之前在rowid裡看到的一致。