Block to Object Mapper (qblockb.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script identifies which database object (table, index, partition, etc.) contains a specific block when given a file ID and block number. It’s essential for corruption analysis, block recovery operations, and understanding which objects are affected when Oracle reports block-level errors in alert logs or trace files.

Script

rem qblock.sql
rem
rem Queries the object name/type when supplied the file_id and block_id.
rem
define fileid=&file_id
define block_id=&bad_block
rem
set  linesize 132
rem
ttitle 'Database Object'
rem
col owner format a12 heading 'OWNER'
col segment_name format a30 heading 'SEGMENT NAME'
col partition_name format a30 heading 'PARTITION NAME'
col object_id format 99999 heading 'ID'
col data_object_id format 99999 heading 'DATA|OBJECT ID'
col relative_fno format 999999 heading 'REL|FILE|NO'
col segment_type format a16 heading 'SEGMENT TYPE'
col created format a9 heading 'CREATED'
col modified format a9 heading 'MODIFIED'
col status format a1 heading 'S'
rem
break on owner skip 1
rem
select e.owner, e.segment_name, e.partition_name, e.segment_type
  from sys.dba_extents e
 where e.file_id=&fileid
   and &block_id between e.block_id and (e.block_id + e.blocks -1)
 order by 1, 2
/
undefine fileid
undefine block_id

Usage

-- Run the script in SQL*Plus or SQLcl
@qblockb.sql

-- When prompted, enter:
-- file_id: The datafile ID number
-- bad_block: The block number within that file

-- Example from alert log error:
-- ORA-01578: ORACLE data block corrupted (file # 7, block # 12456)
Enter value for file_id: 7
Enter value for bad_block: 12456

Parameters

• &file_id: Datafile ID number (from alert log or v$datafile)
• &bad_block: Block number within the specified file

Required Privileges

• SELECT on SYS.DBA_EXTENTS
• Typically requires DBA role

Sample Output

Database Object

OWNER        SEGMENT NAME                   PARTITION NAME                 SEGMENT TYPE
------------ ------------------------------ ------------------------------ ----------------
HR           EMPLOYEES                                                     TABLE
             EMP_SALARY_IDX                                                INDEX

SALES        ORDERS_2024_Q1                 ORDERS_2024_Q1                 TABLE PARTITION
             ORD_DATE_IDX                   ORDERS_2024_Q1                 INDEX PARTITION

Key Output Columns

• OWNER: Schema owner of the segment
• SEGMENT NAME: Name of the table, index, or other segment
• PARTITION NAME: Partition name (if partitioned object)
• SEGMENT TYPE: Type of segment (TABLE, INDEX, TABLE PARTITION, etc.)

Understanding Block Mapping

Block Location Process

1. File identification: Uses file_id to locate datafile
2. Extent search: Finds extent containing the block
3. Segment mapping: Identifies object owning the extent
4. Partition details: Shows partition if applicable

Common Segment Types

• TABLE: Regular table data
• INDEX: Index structure
• TABLE PARTITION: Partitioned table segment
• INDEX PARTITION: Partitioned index segment
• LOBSEGMENT: LOB data segment
• LOBINDEX: LOB index segment

Common Use Cases

Block Corruption Analysis

-- From alert log: ORA-01578 block corrupted
@qblockb.sql
-- Enter file_id and block number from error
-- Identify affected object for recovery planning

RMAN Block Recovery

-- After identifying corrupted object
RMAN> RECOVER DATAFILE 7 BLOCK 12456;
-- Or recover at object level
RMAN> RECOVER TABLE hr.employees;

Performance Issue Investigation

-- From buffer cache analysis showing hot blocks
@qblockb.sql
-- Identify which objects are causing contention
-- Plan appropriate tuning strategies

Troubleshooting Block Issues

Block Corruption Recovery

1. Identify object impact:

   @qblockb.sql
   -- Note the affected table/index

2. Check object accessibility:

   SELECT COUNT(*) FROM hr.employees;
   -- May fail if corruption affects data

3. Recovery options:

   -- RMAN block-level recovery
   RMAN> RECOVER DATAFILE 7 BLOCK 12456;
   
   -- Or table-level recovery
   RMAN> RECOVER TABLE hr.employees;

Multiple Block Analysis

-- For multiple corrupted blocks
SELECT DISTINCT e.owner, e.segment_name, e.segment_type
FROM dba_extents e
WHERE e.file_id = &file_id
AND (&block1 BETWEEN e.block_id AND (e.block_id + e.blocks - 1)
  OR &block2 BETWEEN e.block_id AND (e.block_id + e.blocks - 1)
  OR &block3 BETWEEN e.block_id AND (e.block_id + e.blocks - 1));

Object Impact Assessment

-- Determine table size and criticality
SELECT owner, segment_name, segment_type,
       ROUND(bytes/1024/1024, 2) size_mb,
       blocks
FROM dba_segments
WHERE owner = '&owner'
AND segment_name = '&segment_name';

Advanced Analysis

Block Range Analysis

-- Find all objects in a block range
SELECT e.owner, e.segment_name, e.segment_type,
       e.block_id start_block,
       e.block_id + e.blocks - 1 end_block,
       e.blocks
FROM dba_extents e
WHERE e.file_id = &file_id
AND NOT (e.block_id + e.blocks - 1 < &start_block
      OR e.block_id > &end_block)
ORDER BY e.block_id;

Datafile Block Mapping

-- Show all extents in a datafile
SELECT e.owner, e.segment_name, e.segment_type,
       e.block_id, e.blocks,
       e.block_id + e.blocks - 1 end_block
FROM dba_extents e
WHERE e.file_id = &file_id
ORDER BY e.block_id;

Cross-Reference with V$BH

-- Find blocks in buffer cache
SELECT obj object_id,
       dbarfil file_id,
       dbablk block_id,
       status,
       dirty,
       temp
FROM v$bh
WHERE dbarfil = &file_id
AND dbablk = &block_id;

Partition-Specific Analysis

-- Enhanced output for partitioned objects
SELECT e.owner,
       e.segment_name,
       e.partition_name,
       e.segment_type,
       e.block_id,
       e.blocks,
       p.high_value partition_bound
FROM dba_extents e,
     dba_tab_partitions p
WHERE e.file_id = &file_id
AND &block_id BETWEEN e.block_id AND (e.block_id + e.blocks - 1)
AND e.owner = p.table_owner(+)
AND e.segment_name = p.table_name(+)
AND e.partition_name = p.partition_name(+);

Related Scripts

• Database Files (../administration/dfile.md) - File information and mapping
• Extent Analysis (../administration/dextent.md) - Detailed extent information
• Segment Analysis (dsegment.md) - Segment space usage
• Object Dependencies (dobject.md) - Object relationship analysis

Recovery Planning

RMAN Recovery Commands

-- Block-level recovery
RMAN> RECOVER DATAFILE file_id BLOCK block_id;

-- Validate before recovery
RMAN> VALIDATE DATAFILE file_id BLOCK block_id;

-- Table recovery (if supported)
RMAN> RECOVER TABLE schema.table_name;

Alternative Recovery Methods

-- Export/Import for small tables
expdp system/password tables=hr.employees directory=data_pump_dir

-- Tablespace point-in-time recovery
RMAN> RECOVER TABLESPACE users UNTIL TIME 'SYSDATE-1';