Administration

Using DBMS_ROWID to mine the ROWID pseudocolumn

User Rating:  / 1
PoorBest 
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: 5247

Using DBMS_ROWID to mine the ROWID pseudocolumn

 

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;