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

Original link: https://www.modb.pro/db/22554?cyn

Summary: describes the storage structure of the physical ROWID type of Oracle database

Oracle's ROWID is used to uniquely identify a record in the table, which is the physical address where the data is stored in the database.

There are two types of ROWID for Oracle: physical ROWID and logical ROWID. Indexes organize tables using logical ROWID, and other types of tables use physical ROWID. Physical ROWID is extended in version 8 of Oracle, and Oracle7 and below use constraint ROWID,Oracle8 and above to use extended ROWID. This article describes the physical extension ROWID, and since the constraint ROWID is only for compatibility with earlier versions, it will not be discussed.

SQL > create table test_rowid (id number, row_id rowid); table has been created. SQL > insert into test_rowid values (1, null); 1 line has been created. SQL > update test_rowid set row_id = rowid where id = 1; 1 row has been updated. SQL > commit; submission completed. SQL > select rowid, row_id from test_rowid;ROWID ROW_ID--AAABnRAAGAAAACWAAA AAABnRAAGAAAACWAAA

The physical extension ROWID of Oracle has 18 bits, each using 64-bit encoding, represented by AZ, az, 0x9, +, / 64 characters respectively. A means zero, B means one,... Z for 25, a for 26,... Z means 51. 0 means 52,. 9 means 61BI + means 62BI / means 63.

ROWID can be divided into four parts.

1.OOOOOO: the first 6 bits represent DATA OBJECT NUMBER, and the table information can be determined by converting digits to match the DATA_OBJECT_ID in DBA_OBJECTS.

For example, the DATA OBJECT NUMBER in the above example is AAABnR, and the converted bit number is 1 × 64 × 64 + 39 × 64 + 17.

SQL > select owner, object_name from dba_objects 2 where data_object_id = 1'64'64 + 39'64 + 17 owner OBJECT_NAME-- YANGTK TEST_ROWID

2.FFF: bits 7 to 9 represent the data file number of the relative tablespace.

In the above example, AAG, which represents the data file 6.

SQL > select file_name, tablespace_name from dba_data_files where relative_fno = 6 countries FILECTNAME TABLESPACE_NAME--E:\ ORACLE\ ORADATA\ TEST\ YANGTK01.DBF YANGTK

3.BBBBBB: bits 10 to 15 indicate the BLOCK in which this entry is recorded in the data file.

The above example is AAAACW, and the converted bit number is 2 × 64 BLOCK 22, which represents the 150th BLOCK recorded in the data file.

4.RRR: the last three digits indicate how many records this record is in BLOCK.

The above example is AAA, which represents the 0th record (always counting from 0).

SQL > alter system dump datafile 6 block 150; system has changed. SQL > select row_id, dump (row_id, 16) dump_rowid from test_rowid;ROW_ID DUMP_ROWID- AAABnRAAGAAAACWAAA Typ=69 Len=10: 0pje 0pje 19rect dlgle 1pr 80je 0je ninety six Jing 0jue 0

Find the corresponding dump file, you can find the type of information

* 2004-12-21 1715 SESSION ID: 2004-12-21 17:58:26.000Start dump data blocks tsn: 6 file#: 6 minblk 150 maxblk 150buffer tsn: 6 rdba: 0x01800096 scn: 0x0000.2e389c16 seq: 0x01 flg: 0x06 tail: 0x9c160601frmt: 0x02 chkval: 0xc97d type: 0x06=trans dataBlock header dump: 0x01800096 Object id on Block? Y seg/obj: 0x19d1 csc: 0x00.2e389c0f itc: 2 flg: O typ: 1-DATA fsl: 0 fnx: 0x0 ver: 0x01 Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0003.009.00000057 0x0080004b.0042.56-- U- 1 fsc 0x0000.2e389c160x02 0x0000.000.00000000 0x00000000.0000.00-0 fsc 0x0000.00000000 data_block_dump Data header at 0x651105c=tsiz: 0x3fa0hsiz: 0x14pbl: 0x0651105cbdba: 0x01800096 76543210flag=-ntab=1nrow=1frre=-1fsbo=0x14fseo=0x3f89avsp=0x3f7btosp=0x3f7b0xe:pti [0] nrow=1 offs=00x12:pri [0] offs=0x3f89block_row_dump:tab 0, row 0, @ 0x3f89tl: 17 fb:-- Hkoto Flashi-lb: 0x1 cc: 2col 0: [2] C1 02col 1: [10] 00 19 d 1 01800096 00 00end_of_block_dumpEnd dump data blocks tsn: 6 file#: 6 minblk 150 maxblk 150

Sometimes you need to check the DUMP information of the table, but it is difficult to locate exactly which BLOCK the data in the table starts from. According to the information contained in the ROWID, you can easily find the starting BLOCK.

Here's a brief description of how ROWID types are stored.

SQL > select row_id, dump (row_id, 16) dump_rowid from test_rowid;ROW_ID DUMP_ROWID- AAABnRAAGAAAACWAAA Typ=69 Len=10: 0pje 0pje 19rect dlgle 1pr 80je 0je ninety six Jing 0jue 0

The first four bits represent the first six bits of ROWID, that is, DATA_OBJECT_ID information. The data is saved in numeric format.

SQL > select to_number ('19d1', 'xxxxxx') from dual;TO_NUMBER (' 19D1')-- 6609SQL > select 1'64'34'39 64 17 from dual;1*64*64+39*64+17- 6609

There is a problem here. According to the value range of ROWID, the maximum value of OBJECT_DATA_ID is 64 to the sixth power, while according to DUMP,oracle, only 4 bits are saved, so the value range is 256 to the fourth power.

SQL > set numwid 12SQL > select power (64), power (256), power (64) / power (256) from dual; POWER (64) POWER (256) POWER (64) / POWER (256)-68719476736 4294967296 16

As you can see, the maximum value of OBJECT_DATA_ID is 4294967296, and repetition occurs when this value is exceeded. (of course, it's impossible in reality).

The last four bits are special, which are composed of the "and" values of the data file number and the block number.

The value of the data file is multiplied by 64 and saved on 5 or 6 bits.

SQL > select to_number ('0180' xxxx') from dual;TO_NUMBER ('0180'')-384SQL > select 6'64 from dual; 6'64-384

At the same time, the value of 6-bit BLOCK is also saved on these 4 bits and added to the result of data file rollover. It is still stored in digital format.

SQL > select to_number (xxx') from dual;TO_NUMBER-150SQL > select 234 22 from dual; 264 22-150

Because the value of the data file is saved in two bits, and the minimum unit is 64, the number of data files that can be saved in ROWID is 1024, and more than 1024 will cause ROWID duplication.

SQL > select 256x256Comp64 from dual;256*256/64- 1024

Since these four bits are shared between the value of BLOCK and the data file, the maximum value of the third bit of BLOCK should be less than 64, so that the ROWID is not duplicated. So the maximum value of the block value should be 4194304.

SQL > select 64, 256, 256, from dual;64*256*256- 4194304

The last two bits hold the values recorded in the BLOCK. The maximum of this value is 65536.

SQL > select 256 / 256 from dual; 256 / 256-65536

Let's take a look at an example of how Oracle "shares" bytes 5 and 6 with relative file numbers and BLOCK numbers.

SQL > select blocks from user_segments where segment_name = 'TEST1'; BLOCKS- 86016SQL > select max (rowid), dump (max (rowid)) dump_rowid from test1 MAX (ROWID) DUMP_ROWID- AAABy+AAJAAAU5EAAM Typ=69 Len=10: 0 0 28Coe 190jne 2je 65je 78jr 0je 12SQL > select dbms_rowid.rowid_relative_fno ('AAABy+AAJAAAU5EAAM') fno, 2 dbms_rowid.rowid_block_number (' AAABy+AAJAAAU5EAAM') block_num from dual FNO BLOCK_NUM--9 85572SQL > select 9 "64, 2" 256 "65 from dual; 9" 64 2 "256" 65-- 576 577SQL > select 1 "256" 256 + 78 "256 + 68 from dual 1 "256" 256 "78" 256 "68-85572

As you can see, the quotient of 5 and 6 divided by 64 is the relative file number, and the remainder is the high position of the BLOCK number. Multiplying by 65536 and adding the lower two digits is the BLOCK number.

Related Reading: an Analysis of the Storage format of Oracle basic data types-- logical ROWID https://www.modb.pro/db/22638?cyn

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.