Shulou(Shulou.com)06/01 Report--

Oracle Study Case Study-Oracle Block Address

Oracle accesses data in blocks. This article briefly describes how to obtain the required blocks in memory through Block Address.

DBA(data block address)：

A DBA is the address of an oracle data block for access purposes.

RDBA (Tablespace relative database block address):

RDBA is the address of the relative data block, where the data is located, rdba can be rfile#+block#in rowid

ROWID:

Oracle determines the position of row by rowid when accessed through Index; we all know that rowid represents the physical address of a row, which uniquely identifies a rowid, and generally does not change in use unless the rowid changes after the physical position of the row. Note that rowid doesn't actually exist in the data block of the table, but it does exist in the index, which is used to find row data in the table by rowid.

Before Oracle8, a rowid occupied 6 bytes of storage space (10bit file#+22bit block#+16bit row#), then after oracle 8, the storage space of this rowid is expanded to 10 bytes.(32bit object#+10bit rfile#+22bit block#+16bit row#), so the limit on the number of database files in the database has changed from a maximum of 2^10-1 data files in the entire database to a maximum of 2^10-1 data files in each table space.

(Note that rowid stored in local index is 6 bytes, while rowid stored in global index is 10 bytes)

The added prefix 32-bit object#is mainly used to locate the table space, and this object#actually corresponds to data_object_id. Since a segment object can only belong to one table space, and data_object_id is the physical storage id that identifies a segment, object#+rfile#can uniquely locate the current rowid on which data file.

We can convert our rowid into different components via dbms_rowid package:

dbms_rowid.rowid_object(rowid) ---> 32bit object#dbms_rowid.rowid_relative_fno(rowid) ---> 10bit rfile#dbms_rowid.rowid_block_number(rowid) ---> 22bit block#dbms_rowid.rowid_row_number(rowid) ---> 16bit row#Case analysis:

1. Translate address by dbms_utility

10:33:59 SYS@ test1 >desc dbms_utilityFUNCTION DATA_BLOCK_ADDRESS_BLOCK RETURNS NUMBER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- DBA NUMBER IN FUNCTION DATA_BLOCK_ADDRESS_FILE RETURNS NUMBER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- DBA NUMBER IN FUNCTION MAKE_DATA_BLOCK_ADDRESS RETURNS NUMBER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- FILE NUMBER IN BLOCK NUMBER IN

2. Get RDBA through rowid

11:14:36 SYS@ test1 >conn scott/tigerConnected.11:14:39 SCOTT@ test1 >select rowid,ename from emp where rownum=1;ROWID ENAME------------------ ----------AAAESjAAEAAAACVAAA SMITH11:15:05 SCOTT@ test1 >select dbms_rowid.ROWID_RELATIVE_FNO(rowid) ,dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from emp where rownum=1;DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)------------------------------------ ------------------------------------ 4 1493. Obtaining block information through Dump

11:42:16 SYS@ test1 >alter system dump datafile 4 block 149;

System altered.

Block dump from cache:Dump of buffer cache at level 4 for tsn=4, rdba=16777365Block dump from disk:buffer tsn: 4 rdba: 0x01000095 (4/149)scn: 0x0000.009722f6 seq: 0x01 flg: 0x04 tail: 0x22f60601frmt: 0x02 chkval: 0x52a5 type: 0x06=trans dataHex dump of block: st=0, typ_found=1Dump of memory from 0x008E8200 to 0x008EA2008E8200 0000A206 01000095 009722F6 04010000 [......... "......]...... Block header dump: 0x01000095 Object id on Block? Y seg/obj: 0x44a3 csc: 0x00.9722f6 itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x1000090 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc0x01 0x001b.01f.0000007a 0x01c01c55.0121.23 C-U- 0 scn 0x0000.00971d500x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000bdba: 0x01000095data_block_dump,data header at 0x8e8264...... block_row_dump:tab 0, row 0, @0x1f72tl: 38 fb: --H-FL-- lb: 0x0 cc: 8col 0: [ 3] c2 4a 46col 1: [ 5] 53 4d 49 54 48col 2: [ 5] 43 4c 45 52 4bcol 3: [ 3] c2 50 03col 4: [ 7] 77 b4 0c 11 01 01 01col 5: [ 2] c2 09col 6: *NULL*col 7: [ 2] c1 15tab 0, row 1, @0x1f47...... 4. Get rfile#and block_id in rowid through RDBA

11:21:20 SCOTT@ test1 >select dbms_utility.data_block_address_file(16777365) "file",dbms_utility.data_block_address_block(16777365) "block" from dual; file block---------- ---------- 4 1495, hexadecimal conversion

11:43:11 SYS@ test1 >select to_number('01000095','xxxxxxxx') from dual;TO_NUMBER('01000095','XXXXXXXX')-------------------------------- 167773656, manual mode conversion

rdba: 0x01000095 (4/149) Binary: 0000 0001 0000 0000 0000 1001 0101 First 10 bits: (rfile#) 0000 0001 00 =4 Last 22 bits: (block#) 00 0000 0000 0000 1001 0101 128+ 16+5=149

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.