rowID in Oracle

Each table in Oracle has a ROWID, which is a 18-bit character string, for example: Aaangbaabaaao/kaaa. The string it contains consists of a-z,a-z,0-9,+,/of these characters. is 64 binary.

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

This 18-bit string is divided into 4 segments that represent different meanings (6,3,6,3), 1-6 bits represent the object data ID (segment number), 7-9 bits represent the data file number, 10-15 bits represent the block number, and 16-18 represents the row in the data block.

Suppose you build the following table:

CREATE TABLE TB (ID int,name varchar2 (10))

INSERT into TB values (1, ' a ')
INSERT into TB values (2, ' B ')

By select Rowid,id from TB:

AAANGBAABAAAO/KAAA 1
Aaangbaabaaao/kaab 2

Taking AAANGB AAB aaao/k AAA as an example,

AAANGB is the ID of the table TB,

See object_id as 55297 through select * from all_objects where object_name= ' TB '

The value of the 10 binary of the computed AAANGB is: The value obtained by the select 13*64*64+32*64+1 from dual is also 55297.

AAB is the data file number: 1, which represents the file_id of Dba_data_files, and the corresponding data file information can be seen through select * from Dba_data_files.

AAAO/K representative block number: 61386, calculated by select 14*64*64 +63*64 + from dual

The line number is: 0, the above two lines are contiguous, only the line number is different.

The following SQL allows you to see if the data block is loaded into the data buffer: SELECT * from V$BH where block#=61386 and File#=1

Once you know the block number, you can dump the contents of this block in memory (data buffer): Alter system dump DATAFILE 1 Block 61386

Once you know the block number, you can dump the contents of this block in the data file:

alter system dump DataFile ' D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01. DBF ' Block 61386

(Since Oracle's write data to a data file does not necessarily complete after a commit, you can force the data buffer to be written to the data file by Alter SYSTEM checkpoint)

You can then see the contents of the dump in the trace file under the Udump folder.

A package Dbms_rowid in Oracle directly provides a function to get the block number, line number

Dbms_rowid.rowid_object: Object number
DBMS_ROWID.ROWID_RELATIVE_FNO: File Number
Dbms_rowid.rowid_block_number: Block number
Dbms_rowid.rowid_row_number: Line number

Example: Select Rowid,id,dbms_rowid.rowid_block_number (ROWID) from TB

Recommended to see http://www.itpub.net/thread-912812-1-1.html have very detailed views.

rowID in Oracle