Check block number and row id in Oracle

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.