dba 和 rdba 转载

一.  DB(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.

 

1.1 Dumping data blocks

Start by getting the file and block number to dump. Example:

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 emp WHERE empno = 7369;

 

   REL_FNO    BLOCKNO      ROWNO      EMPNO ENAME

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

         4         20          0       7369 SMITH

Dump the block:

SQL> alter system dump datafile 4 block 20;

System altered.

 

Look for the newly created dump file in your UDUMP directory.

 

-- dump 多个blocks

Use the following syntax to dump multiple blocks:

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

 

1.2  Analyzing data block dumps

From the above block dump:

block_row_dump:

tab 0, row 0, @0x1d49

tl: 38 fb: --H-FL-- lb: 0x0  cc: 8

col  0: [ 3]  c2 4a 46

col  1: [ 5]  53 4d 49 54 48

col  2: [ 5]  43 4c 45 52 4b

col  3: [ 3]  c2 50 03

col  4: [ 7]  77 b4 0c 11 01 01 01

col  5: [ 2]  c2 09

col  6: *NULL*

col  7: [ 2]  c1 15

 

Converting back to table values:

Col 0 (EMPNO)

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

VALUE

----------

      7369

Col 2 (ENAME) - simply convert the hex values to ascii - 53 4d 49 54 48 -> SMITH. Alternatively:

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

VALUE

---------

SMITH

 

 

二.  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的。有关rowid的内容,参考我的Blog:

       Oracle Rowid 介绍

       http://www.linuxidc.com/Linux/2011-07/37998.htm

 

2.1 Find the DBA for a given row

Start by getting the file and block number of the row. Example:

SQL> SELECT

  2    dbms_rowid.rowid_relative_fno(rowid) REL_FNO,

  3    dbms_rowid.rowid_block_number(rowid) BLOCKNO,

  4    empno, ename

  5  FROM emp WHERE empno = 7369;

 

   REL_FNO    BLOCKNO      EMPNO ENAME

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

         4         20       7369 SMITH

 

2.2 convert the file and block numbers to a DBA address:

SQL> variable dba varchar2(30)

SQL> exec :dba := dbms_utility.make_data_block_address(4, 20);

PL/SQL procedure successfully completed.

SQL> print dba

DBA

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

16777236

 

2.3  Convert a DBA back to file and block numbers

Example:

SQL> SELECT dbms_utility.data_block_address_block(16777236) "BLOCK",

  2         dbms_utility.data_block_address_file(16777236) "FILE"

  3    FROM dual;

     BLOCK       FILE

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

        20          4

 

 

三.  RDBA(Tablespace relative database block address)

 

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

       Oracle Rowid 介绍

       http://blog.****.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#。

 

 

SYS@anqing1(rac1)> SELECT

rowid,

dbms_rowid.rowid_relative_fno(rowid) REL_FNO,

dbms_rowid.rowid_block_number(rowid) BLOCKNO,

dbms_rowid.rowid_row_number(rowid) ROWNO,

empno, ename

FROM scott.emp WHERE empno = 7521; 

ROWID      REL_FNO    BLOCKNO      ROWNO   EMPNO ENAME

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

AAAMfMAAEAAAAAgAAA    4      32      0       7369 SMITH

 

rowid = AAAMfMAAEAAAAAgAAA

BlockNo= 4

rowno =0

 

把这个block dump到trace:

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

System altered.

 

查看当前的trace 文件位置:

SYS@anqing1(rac1)> oradebug setmypid;

Statement processed.

SYS@anqing1(rac1)> oradebug tracefile_name

/u01/app/oracle/admin/anqing/udump/anqing1_ora_19997.trc

 

查看trace file:

[oracle@rac1 ~]$ cat /u01/app/oracle/admin/anqing/udump/anqing1_ora_19997.trc               

*** 2011-06-07 11:02:30.023

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

buffer tsn: 4 rdba: 0x01000020 (4/32)   -- rdba 的值

scn: 0x0000.0006bfdb seq: 0x10 flg: 0x06 tail: 0xbfdb0610

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

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

Dump of memory from 0x0ED09400 to 0x0ED0B400

ED09400 0000A206 01000020 0006BFDB 06100000  [.... ...........]

ED09410 000026A0 00180001 0000C7CC 0006BFD9  [.&..............]

.....

ED094A0 00000000 00000000 00000000 00000000  [................]

        Repeat 465 times

ED0B1C0 00000000 08012C00 2350C203 4C494D06  [.....,....P#.MIL]

ED0B1D0 0552454C 52454C43 4EC2034B B6770753  [LER.CLERK..NS.w.]

....

ED0B3E0 05485449 52454C43 50C2034B B4770703  [ITH.CLERK..P..w.]

ED0B3F0 0101110C 09C20201 15C102FF BFDB0610  [................]

Block header dump:  0x01000020

 Object id on Block? Y

 seg/obj: 0xc7cc  csc: 0x00.6bfd9  itc: 2  flg: E  typ: 1 - DATA

     brn: 0  bdba: 0x1000019 ver: 0x01 opc: 0

     inc: 0  exflg: 0

 

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0003.011.000000f2  0x00805794.00c8.49  --U-   14  fsc 0x0000.0006bfdb

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

 

data_block_dump,data header at 0xed09464

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

tsiz: 0x1f98

hsiz: 0x2e

pbl: 0x0ed09464

bdba: 0x01000020

     76543210

flag=--------

ntab=1

nrow=14

frre=-1

fsbo=0x2e

fseo=0x1d61

avsp=0x1d33

tosp=0x1d33

0xe:pti[0]      nrow=14 offs=0  -- 该块中保存了14条记录。从row 0到row 13

0x12:pri[0]     offs=0x1f72

0x14:pri[1]     offs=0x1f47

0x16:pri[2]     offs=0x1f1c

0x18:pri[3]     offs=0x1ef3

0x1a:pri[4]     offs=0x1ec6

0x1c:pri[5]     offs=0x1e9d

0x1e:pri[6]     offs=0x1e74

0x20:pri[7]     offs=0x1e4c

0x22:pri[8]     offs=0x1e26

0x24:pri[9]     offs=0x1dfb

0x26:pri[10]    offs=0x1dd5

0x28:pri[11]    offs=0x1daf

0x2a:pri[12]    offs=0x1d88

0x2c:pri[13]    offs=0x1d61

block_row_dump:

tab 0, row 0, @0x1f72

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

col  0: [ 3]  c2 4a 46

col  1: [ 5]  53 4d 49 54 48

col  2: [ 5]  43 4c 45 52 4b

col  3: [ 3]  c2 50 03

col  4: [ 7]  77 b4 0c 11 01 01 01

col  5: [ 2]  c2 09

col  6: *NULL*

col  7: [ 2]  c1 15

tab 0, row 1, @0x1f47

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

col  0: [ 3]  c2 4b 64

col  1: [ 5]  41 4c 4c 45 4e

col  2: [ 8]  53 41 4c 45 53 4d 41 4e

col  3: [ 3]  c2 4d 63

col  4: [ 7]  77 b5 02 14 01 01 01

col  5: [ 2]  c2 11

col  6: [ 2]  c2 04

col  7: [ 2]  c1 1f

tab 0, row 2, @0x1f1c

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

col  0: [ 3]  c2 4c 16

col  1: [ 4]  57 41 52 44

col  2: [ 8]  53 41 4c 45 53 4d 41 4e

col  3: [ 3]  c2 4d 63

col  4: [ 7]  77 b5 02 16 01 01 01

col  5: [ 3]  c2 0d 33

col  6: [ 2]  c2 06

col  7: [ 2]  c1 1f

tab 0, row 3, @0x1ef3

...

tab 0, row 13, @0x1d61

 

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

col  0: [ 3]  c2 50 23

col  1: [ 6]  4d 49 4c 4c 45 52

col  2: [ 5]  43 4c 45 52 4b

col  3: [ 3]  c2 4e 53

col  4: [ 7]  77 b6 01 17 01 01 01

col  5: [ 2]  c2 0e

col  6: *NULL*

col  7: [ 2]  c1 0b

end_of_block_dump

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

[oracle@rac1 ~]$

 

/* Formatted on 2011/6/7 11:27:10 (QP5 v5.163.1008.3004) */

SELECT DBMS_UTILITY.data_block_address_file (

          TO_NUMBER (LTRIM (‘‘0x01000020‘‘, ‘‘0x‘‘), ‘‘xxxxxxxx‘‘))

          AS file_no,

       DBMS_UTILITY.data_block_address_block (

          TO_NUMBER (LTRIM (‘‘0x01000020‘‘, ‘‘0x‘‘), ‘‘xxxxxxxx‘‘))

          AS block_no

  FROM DUAL;

 

   FILE_NO   BLOCK_NO

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

         4         32

 

这个和我们之前在rowid里查看的一致。

 

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

 

tab 0, row 13, @0x1d61

SYS@anqing1(rac1)> select DBMS_UTILITY.data_block_address_block (TO_NUMBER (LTRIM (‘‘0x1d61‘‘, ‘‘0x‘‘),‘‘xxxxxxxx‘‘)) as block_no from dual;

 

  BLOCK_NO

----------

      7521                                                                           

 

我们查询的那条row记录在最后一条。

dba 和 rdba 转载,布布扣,bubuko.com

dba 和 rdba 转载

上一篇:SQL Server 存储过程


下一篇:mysql update from 子查询