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