Check object name from block number in Oracle

Check object name from block number in Oracle

DBA_EXTENTS table is used to identified the object name and type and which file id is used by the object from block number.

Find the objects list present in block number in Oracle

COL OWNER FOR A10
COL SEGMENT_NAME FOR A15
COL PARTITION_NAME FOR A15
COL SEGMENT_TYPE FOR A10
COL FILE_ID FOR 999
SELECT OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,FILE_ID,BLOCK_ID FROM DBA_EXTENTS WHERE block_number BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;

Example:

--1. Create table
SQL> create table test1 (id number,name varchar2(10));
Table created.

--2. Insert data into the 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.
SQL> commit;
Commit complete.

--3. Check the Block Number for the table
SQL> select rowid,DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) "Block Number" from test1;

ROWID              Block Number
------------------ ------------------------------------
AAASJRAAMAAAAClAAA                                  165
AAASJRAAMAAAAClAAB                                  165
AAASJRAAMAAAAClAAC                                  165

--4. Enter the block number getting from upper query, it will give the list of all objects present in this block.
col owner for a10
col segment_name for a15
col partition_name for a15
col segment_type for a10
col file_id for 999
select owner,segment_name,partition_name,segment_type,file_id,block_id from dba_extents where 165 between block_id AND block_id + blocks - 1;

OWNER      SEGMENT_NAME    PARTITION_NAME  SEGMENT_TY FILE_ID   BLOCK_ID
---------- --------------- --------------- ---------- ------- ----------
SYS        SQLOBJ$_PKEY                    INDEX           10        160
SYS        C_OBJ#                          CLUSTER          9        160
TEST       TEST1                           TABLE           12        160

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.