Rowid and Oraclerowid in Oracle

Rowid and Oraclerowid in Oracle

Each table in Oracle has a rowid, which is an 18-character string, for example, AAANgBAABAAAO/KAAA. it contains a string consisting of A-Z, a-z, 0-9, +. yes.

Where the A-Z corresponds to 0-25, a-z corresponds to 26-51,0-9 corresponds to 52-61, + corresponds to 62,/Corresponds to 63.

The 18-Bit String is divided into four segments to indicate different meanings (6, 3, 6, 3), 1-6 bits represent the object data ID (segment number), and 7-9 bits represent the data file number, the value 10-15 represents the data block number, and the value 16-18 represents the row in the data block.

Assume that the following table is created:

Create table tb (id int, name varchar2 (10 ))

Insert into tb values (1, 'A ')
Insert into tb values (2, 'B ')

Use select rowid and id from tb to get:

AAANgBAABAAAO/KAAA 1
AAANgBAABAAAO/KAAB 2

Take AAANgB aab aaao/k aaa as an example,

AAANgB is the id of the table tb,

Select * from all_objects where object_name = 'tb' To See That object_id is 55297,

The 10 hexadecimal value of AAANgB is calculated as follows: select 13*64*64 + 32*64 + 1 the value obtained from dual is also 55297.

AAB is the data file number: 1, which represents the file_id of dba_data_files. You can view the corresponding data file information through select * from dba_data_files.

AAAO/K indicates the block number: 61386. calculated by using select 14*64*64 + 63*64 + 10 from dual.

The row number is: 0. The preceding two rows are adjacent, but the row number is different.

Check whether the data block is loaded into the data buffer using the following SQL statement: select * from v $ bh where block # = 61386 and file # = 1

After knowing the block number, you can dump the block content in the memory (data buffer): alter system dump datafile 1 block 61386

After knowing the block number, you can dump the block content in the data file:

Alter system dump datafile 'd: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ SYSTEM01.DBF 'block 61386

(Since writing data to a data file from Oracle is not necessarily completed after commit, you can use alter system checkpoint to forcibly write data with a data buffer to a data file)

Then, you can view the dump content in the trace file in the udump folder.

A function is provided in a package dbms_rowid in Oracle to obtain the block number and row number.

Dbms_rowid.rowid_object: Object ID
Dbms_rowid.rowid_relative_fno: file number
Dbms_rowid.rowid_block_number: block number
Dbms_rowid.rowid_row_number: row number

Example: select rowid, id, dbms_rowid.rowid_block_number (rowid) from tb

For details, see http://www.itpub.net/thread-912812-1-1.html.