Administration

Using DBMS_ROWID to mine the ROWID pseudocolumn

User Rating:

/ 1

Details: Parent Category: Articles; Created on Monday, 21 March 2011 15:45; Last Updated on Monday, 12 March 2012 13:54; Published on Monday, 21 March 2011 15:45; Written by Guy Lambregts; Hits: 5272

Retrieve the block number ( In which block is the record stored )

SQL> select dbms_rowid.rowid_block_number('AAAV0JAAEAAAAH8AAf') BLOCK from dual;

 BLOCK
----------
 508

Retrieve the file number ( in which file is the record stored )

SQL> select dbms_rowid.rowid_relative_fno('AAAV0JAAEAAAAH8AAf') FILE_NO from dual;

 FILE
----------
 4

Retrieve the data object id ( to which object does this record belong )

SQL> select dbms_rowid.rowid_object('AAAV0JAAEAAAAH8AAf') DATA_OBJECT_ID from dual;

 DATA_OBJECT_ID
----------
 89353

Retrieve the row number in the block ( what is the relative row number in the block )

SQL> select dbms_rowid.rowid_row_number('AAAV0JAAEAAAAH8AAf') ROW_NUMBER from dual;

 ROW_NUMBER
----------
 31

What data is there in block 508



SQL> select * from t_supplier_su where dbms_rowid.rowid_block_number(rowid)=508;