DBA and RDBA Reprint

A. DB (Data block)

A data block is the smallest unit of storage in an Oracle database.  Every database has a default block size (specified if the database is created), although blocks in different tablespaces May has 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 is retrieved from the data dictionary views user_segments and User_extents. T hese views show how many blocks is allocated for database object and how many blocks is available (free) in a se Gment/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 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 multiple 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] 4d 49 54 48

Col 2: [5] 4c 4b

Col 3: [3] C2 50 03

Col 4: [7] 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", "")) value from dual;

VALUE

----------

7369

Col 2 (ename)-simply convert the hex values to ascii-53 4d. Alternatively:

sql> SELECT utl_raw.cast_to_varchar2 (replace ("4d", "", "") value from dual;

VALUE

---------

SMITH

Two. DBA (Data Block Address)

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

A DBA generally refers to an absolute block of data address. rowID is used to represent the physical address of a row, a row uniquely identifies a rowid, and is generally not changed in use unless the physical location of the row changes after rowID. In rowID, there is a section to represent the DBA. For rowID's content, refer to my blog:

Oracle Rowid Introduction

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 7369 SMITH

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

Sql> Variable dba varchar2 (30)

sql> exec:d BA: = dbms_utility.make_data_block_address (4, +);

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

Three. RDBA (tablespace relative database block address)

Before speaking rdba, we should understand the composition of ROWID. For rowID's content, refer to my blog:

Oracle Rowid Introduction

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

A rdba is a relative block address, which is the address of the block where the data dictionary (tablespace and some object definitions) resides.

After Oracle 8, ROWID's storage space expanded to 10 bytes (32bit object#+10bit rfile#+22bit block#+16bit row#). RDBA is the rfile#+block# in rowID.

[Email protected] (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 0 7369 SMITH

rowID = aaamfmaaeaaaaagaaa

Blockno= 4

Rowno =0

Dump this block to trace:

[Email protected] (RAC1) > alter system dump DATAFILE 4 block 32;

System altered.

To view the current trace file location:

[Email protected] (RAC1) > Oradebug setmypid;

Statement processed.

[Email protected] (RAC1) > Oradebug tracefile_name

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

View Trace File:

[Email protected] ~]$ 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 maxblk 32

buffer Tsn:4 RDBA: 0x01000020 (4/32)-- value of 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--The block holds 14 records. From row 0 to 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] 4d 49 54 48

Col 2: [5] 4c 4b

Col 3: [3] C2 50 03

Col 4: [7] 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] 4c 4c 4e

Col 2: [8] 4c 4d 4e

Col 3: [3] C2 4d 63

Col 4: [7] 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] 4c 4d 4e

Col 3: [3] C2 4d 63

Col 4: [7] 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, @0x1d61

tl:39 FB:--h-fl--lb:0x1 Cc:8

Col 0: [3] C2 50 23

Col 1: [6] 4d 4c 4c 45 52

Col 2: [5] 4c 4b

Col 3: [3] c2 4e 53

Col 4: [7] 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 maxblk 32

[Email protected] ~]$

/* 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

This is consistent with what we looked at in rowID.

Just now, in 32 this block saved 14 Row Records, we continue to inquire about our where=7521 that article:

tab 0, Row, @0x1d61

[Email protected] (RAC1) > select Dbms_utility.data_block_address_block (To_number (LTRIM ("0x1d61", "0x"), "xxxxxxxx")) as Block _no from dual;

Block_no

----------

7521

The row that we are querying is recorded in the last article.