Check block number and row id in Oracle
DBMS_ROWID package is used to get the detail of Block Number from the ROWID of table. Procedure ROWID_BLOCK_NUMBER give you the out of Block number by taking input parameter as ROWID.
Check the associated row id with block number in table
SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid),rowid
FROM table_name;
Example: Specify the range of blocks rowid you needed.
SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid),rowid
FROM SCOTT.EMPLOYEES where DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) between '18000' and '19000' order by 1;
Find row id of particular Block Number
SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid),rowid
FROM SCOTT.EMPLOYEES
where DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) = '18095';
Note: 18095 is block number.
Example
--1. Create table
SQL> create table test1 (id number,name varchar2(10));
Table created.
--2. Insert data into table.
SQL> insert into test1 values ( 1, 'RAM');
1 row created.
SQL> insert into test1 values ( 2, 'sham');
1 row created.
SQL> insert into test1 values ( 3, 'POOJA');
1 row created.
Commit;
--3. Check the block number and rowid from table.
SQL> select rowid,DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) "Block Number" from test1;
ROWID Block Number ------------------ ------------ AAASJRAAMAAAAClAAA 165 AAASJRAAMAAAAClAAB 165 AAASJRAAMAAAAClAAC 165
Smart way of Technology 