oracle和dba,oracle db、dba和rdba

一、DB(Data Block)

1、dumping data blocks

SYS@ tsid > select

2dbms_rowid.rowid_relative_fno(rowid) REL_FNO,

3dbms_rowid.rowid_block_number(rowid) BLOCKNO,

4dbms_rowid.rowid_row_number(rowid) ROWNO,

5id,name

6from t

7where id=1;

REL_FNOBLOCKNOROWNOID NAME

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

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

41501 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: 0x1cc: 2

col0: [ 2]c1 02

col1: [ 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(DBA) is the

address of an Oracle data block for access

purposes.

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

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

SYS@ tsid > select

2dbms_rowid.rowid_relative_fno(rowid) REL_FNO,

3dbms_rowid.rowid_block_number(rowid) BLOCKNO,

4id,name

5from t where id=2;

REL_FNOBLOCKNOID NAME

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

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

4122b

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

2dbms_utility.data_block_address_block(16777228) "BLOCK",

3dbms_utility.data_block_address_file(16777228) "FILE"

4from dual;

BLOCKFILE

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

124

三、RDBA(Tablespace

relative database block address)

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

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

SYS@ tsid > select rowid,

2dbms_rowid.rowid_relative_fno(rowid) REL_FNO,

3dbms_rowid.rowid_block_number(rowid) BLOCKNO,

4dbms_rowid.rowid_row_number(rowid) ROWNO,

5id,name

6from t

7where id=3;

ROWIDREL_FNOBLOCKNOROWNOID NAME

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

AAADJrAAEAAAAAMAAB41213c

把这个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: 0x326bcsc: 0x00.2ea961itc: 2flg: Etyp: 1 - DATA

brn: 0bdba: 0x1000009 ver: 0x01

opc: 0

inc: 0exflg: 0

ItlXidUbaFlagLckScn/Fsc

0x010x0002.022.0000034d0x00800a91.00f2.28--U-2fsc 0x0000.002f1254

0x020x0000.000.000000000x00000000.0000.00----0fsc 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=2offs=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: 0x1cc: 2

col0: [ 2]c1 03

col1: [ 1]62

tab 0, row 1, @0x1f88

tl: 8 fb: --H-FL-- lb: 0x1cc: 2

col0: [ 2]c1 04

col1: [ 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,

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

as block_no

3from dual;

FILE_NOBLOCK_NO

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

412

这和之前在rowid里看到的一致。