Find Block Segment Information (findblk.sql)

What This Script Does

This script identifies which database segment (table, index, partition, etc.) owns a specific file and block number. It’s essential for troubleshooting block corruption errors, analyzing specific block issues, and understanding storage allocation when Oracle reports file/block-related errors in alert logs or trace files.

Script

rem  findblk.sql
rem
select owner, segment_name, segment_type, tablespace_name,
       extent_id, file_id, block_id, blocks
  from sys.dba_extents
 where file_id = &file
   and &block between block_id and block_id + blocks - 1
/

Usage

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

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

-- Example from corruption error:
-- ORA-01578: ORACLE data block corrupted (file # 5, block # 12456)
Enter value for file: 5
Enter value for block: 12456

Parameters

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

Required Privileges

SELECT on SYS.DBA_EXTENTS
Typically requires DBA role

Sample Output

OWNER      SEGMENT_NAME           SEGMENT_TYPE     TABLESPACE_NAME    EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------------------- ---------------- --------------- ------------ ---------- ---------- ----------
HR         EMPLOYEES              TABLE            USERS                     15          5      12288        128

Key Output Columns

OWNER: Schema owner of the segment
SEGMENT_NAME: Name of the table, index, or other segment
SEGMENT_TYPE: Type of segment (TABLE, INDEX, etc.)
TABLESPACE_NAME: Tablespace containing the segment
EXTENT_ID: Extent number within the segment
FILE_ID: Datafile ID (input parameter)
BLOCK_ID: Starting block of the extent
BLOCKS: Number of blocks in this extent

Understanding Block Allocation

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
CLUSTER: Cluster segment

Block Ownership

Each block belongs to exactly one segment
Blocks are allocated in extents (contiguous groups)
Block numbers are unique within each datafile

Common Use Cases

Block Corruption Analysis

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

Performance Investigation

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

Storage Analysis

-- Analyze specific block allocation
@findblk.sql
-- Understand extent structure
-- Verify storage configuration

Troubleshooting Block Issues

Block Corruption Recovery

Identify object impact:

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

Check object accessibility:

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

Recovery options:

-- RMAN block-level recovery
RMAN> RECOVER DATAFILE 5 BLOCK 12456;

-- Or table-level recovery (if supported)
RMAN> RECOVER TABLE hr.employees;

Extent Analysis

-- Find all extents for the affected segment
SELECT extent_id, file_id, block_id, blocks,
       block_id + blocks - 1 end_block
FROM dba_extents
WHERE owner = 'HR'
AND segment_name = 'EMPLOYEES'
ORDER BY extent_id;

Space Utilization

-- Analyze segment space usage
SELECT owner, segment_name, segment_type,
       bytes/1024/1024 size_mb,
       blocks,
       extents
FROM dba_segments
WHERE owner = 'HR'
AND segment_name = 'EMPLOYEES';

Advanced Analysis

Multiple Block Analysis

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

Block Range Analysis

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

Cross-Reference with Buffer Cache

-- Find if block is currently in memory
SELECT obj object_id,
       dbarfil file_id,
       dbablk block_id,
       status,
       dirty,
       temp
FROM v$bh
WHERE dbarfil = &file_id
AND dbablk = &block_number;

Segment Header Analysis

-- Find segment header block
SELECT owner, segment_name,
       header_file, header_block
FROM dba_segments
WHERE owner = 'HR'
AND segment_name = 'EMPLOYEES';

Recovery Planning

RMAN Recovery Commands

-- Validate block before recovery
RMAN> VALIDATE DATAFILE 5 BLOCK 12456;

-- Block-level recovery
RMAN> RECOVER DATAFILE 5 BLOCK 12456;

-- Preview recovery (no changes)
RMAN> RECOVER DATAFILE 5 BLOCK 12456 PREVIEW;

Alternative Recovery Methods

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

-- Flashback table (if available)
FLASHBACK TABLE hr.employees TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);

-- Drop and recreate index (for index corruption)
DROP INDEX hr.emp_name_idx;
CREATE INDEX hr.emp_name_idx ON hr.employees(last_name, first_name);

Impact Assessment

-- Check table size and importance
SELECT owner, table_name,
       num_rows,
       blocks,
       ROUND(avg_row_len * num_rows / 1024 / 1024, 2) estimated_mb
FROM dba_tables
WHERE owner = 'HR'
AND table_name = 'EMPLOYEES';

-- Check dependencies
SELECT owner, name, type, referenced_owner, referenced_name
FROM dba_dependencies
WHERE referenced_owner = 'HR'
AND referenced_name = 'EMPLOYEES';

Block to Object Mapper (qblockb.sql) - Enhanced block analysis with more details
Database Files (../administration/dfile.md) - File information and mapping
Extent Analysis (../administration/dextent.md) - Detailed extent information
Segment Analysis (dsegment.md) - Segment space usage

File and Block Context

Datafile Information

-- Get datafile details
SELECT file_id, file_name, tablespace_name,
       bytes/1024/1024 size_mb,
       status, autoextensible
FROM dba_data_files
WHERE file_id = &file_id;

Block Size Information

-- Check database block size
SELECT value block_size_bytes
FROM v$parameter
WHERE name = 'db_block_size';