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: 5526
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;