Dbms_rowid positioning data row physical storage location in Oracle

For the heap table in Oracle, we can get the value of the rowid of the corresponding row record through Oracle's built-in ROWID pseudo columns (note that this rowid is just a pseudo column, which does not exist in the actual block). We can then use the DBMS_ROWID package to locate the actual physical storage physical address of the corresponding data row through the ROWID pseudo column.

DBMS_ROWID.ROWID_RELATIVE_FNO: Locate the data file that contains the data row

Dbms_rowid.rowid_block_number: Locate the number of blocks in the data file for the data row

Dbms_rowid.rowid_row_number: Locates how many rows of the data row in the data block

See below for an example:

Scott@orcl>desc EMP

Name Null? Type

----------------------------------------- -------- ----------------------------

EMPNO not NULL number (4)

Ename VARCHAR2 (10)

JOB VARCHAR2 (9)

MGR Number (4)

HireDate DATE

SAL Number (7,2)

COMM Number (7,2)

DEPTNO Number (2)

Scott@orcl>col location for A20

Scott@orcl>select empno,rowid,dbms_rowid.rowid_relative_fno (ROWID) | | '_' || Dbms_rowid.rowid_block_number (ROWID) | | '_' || Dbms_rowid.rowid_row_number (ROWID) Location

2 from EMP;

EMPNO ROWID LOCATION

---------- ------------------ --------------------

7369 aaamfmaaeaaaaagaaa 4_32_0

7499 Aaamfmaaeaaaaagaab 4_32_1

7521 AAAMFMAAEAAAAAGAAC 4_32_2

7566 Aaamfmaaeaaaaagaad 4_32_3

7654 Aaamfmaaeaaaaagaae 4_32_4

7698 AAAMFMAAEAAAAAGAAF 4_32_5

7782 Aaamfmaaeaaaaagaag 4_32_6

7788 Aaamfmaaeaaaaagaah 4_32_7

7839 Aaamfmaaeaaaaagaai 4_32_8

7844 Aaamfmaaeaaaaagaaj 4_32_9

7876 Aaamfmaaeaaaaagaak 4_32_10

EMPNO ROWID LOCATION

---------- ------------------ --------------------

7900 Aaamfmaaeaaaaagaal 4_32_11

7902 Aaamfmaaeaaaaagaam 4_32_12

7934 Aaamfmaaeaaaaagaan 4_32_13

Rows selected.

Take the first row of data to show that the value of the ROWID pseudo column of the row is AAAMFMAAEAAAAAGAAA, the value that can be converted through the ROWID pseudo column by the ROWID package is 4_32_0, representing the row in the No. 0 row of the 32nd block of data file 4.

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/