Database Object from Block ID (qblocko.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 database objects from file and block coordinates by:

• Mapping file ID and block ID to specific database objects
• Finding which table, index, or other segment owns a particular block
• Providing object metadata including creation/modification dates
• Supporting partitioned objects with subobject identification
• Essential for corruption troubleshooting and block-level analysis

Script

rem qblock.sql
rem
rem Queries all rows from a block 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 object_name format a30 heading 'OBJECT NAME'
col subobject_name format a15 heading 'SUBOBJECT 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 object_type format a13 heading 'OBJECT 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 o.owner, o.object_name, o.subobject_name, o.object_type,
       o.data_object_id,
       o.created,
       decode( o.last_ddl_time, created, null, o.last_ddl_time ) modified,
       decode( o.status, 'VALID', null, 'INVALID', 'I', '?' ) status,
       e.relative_fno
  from sys.dba_objects o, sys.dba_extents e
 where e.file_id=&fileid
   and &block_id between e.block_id and (e.block_id + e.blocks -1)
   and o.object_type = e.segment_type
   and o.owner = e.owner
   and o.object_name = e.segment_name
   and nvl(o.subobject_name, 'x') = nvl(e.partition_name, 'x')
 order by owner, object_name;
rem
undefine fileid
undefine block_id

Usage

SQL> @qblocko.sql
Enter value for file_id: 4
Enter value for bad_block: 12345

Parameters

• file_id: Database file identifier (from V$DATAFILE or DBA_DATA_FILES)
• bad_block: Block number within the file

Required Privileges

• SELECT on SYS.DBA_OBJECTS
• SELECT on SYS.DBA_EXTENTS

Sample Output

Database Object

OWNER        OBJECT NAME                    SUBOBJECT NAME  OBJECT TYPE   DATA   REL    CREATED   MODIFIED  S
                                                           OBJECT ID    FILE NO
------------ ------------------------------ --------------- ------------- ------ ------- --------- --------- -
HR           EMPLOYEES                                      TABLE         12345      4 15-MAR-24

SALES        ORDER_ITEMS                    P_2024_Q1       TABLE         23456      4 01-JAN-24 15-MAR-24
             ORDER_ITEMS_IDX                P_2024_Q1       INDEX         23457      4 01-JAN-24 15-MAR-24

WAREHOUSE    INVENTORY_HIST                 SYS_P2847       TABLE         34567      4 10-FEB-24

Key Output Columns

Object Identification

• OWNER: Schema that owns the object
• OBJECT NAME: Name of the table, index, or other segment
• SUBOBJECT NAME: Partition or subpartition name (if applicable)
• OBJECT TYPE: Type of database object (TABLE, INDEX, LOB, etc.)

Object Metadata

• DATA OBJECT ID: Internal Oracle object identifier
• REL FILE NO: Relative file number within tablespace
• CREATED: When the object was created
• MODIFIED: When the object was last modified (DDL)
• S: Status indicator (I = Invalid, blank = Valid)

Common Use Cases

1. Corruption Troubleshooting

   • Identify objects affected by ORA-01578 errors
   • Map corrupt blocks to specific tables or indexes
   • Plan recovery strategies based on object importance

2. Block-Level Analysis

   • Understand data distribution across files
   • Analyze space usage patterns
   • Investigate I/O hotspots

3. Forensic Analysis

   • Trace block-level issues to specific objects
   • Understand impact of storage failures
   • Plan targeted recovery operations

Finding File and Block Information

From Oracle Error Messages

Oracle corruption errors typically provide file and block numbers:

ORA-01578: ORACLE data block corrupted (file # 4, block # 12345)

From Alert Log Analysis

-- Search alert log for corruption messages
SELECT message_text, originating_timestamp
FROM v$diag_alert_ext
WHERE message_text LIKE '%corrupt%'
OR message_text LIKE '%ORA-01578%'
ORDER BY originating_timestamp DESC;

From RMAN Validation

-- After RMAN validation
SELECT file#, block#, blocks, corruption_type
FROM v$database_block_corruption
ORDER BY file#, block#;

Advanced Analysis

Multiple Block Analysis

-- Check multiple blocks in a range
SELECT DISTINCT o.owner, o.object_name, o.object_type,
       e.block_id start_block,
       e.block_id + e.blocks - 1 end_block,
       e.blocks total_blocks
FROM dba_objects o, dba_extents e
WHERE e.file_id = &file_id
AND &start_block <= (e.block_id + e.blocks - 1)
AND &end_block >= e.block_id
AND o.object_type = e.segment_type
AND o.owner = e.owner
AND o.object_name = e.segment_name
AND NVL(o.subobject_name, 'x') = NVL(e.partition_name, 'x')
ORDER BY o.owner, o.object_name, e.block_id;

Object Extent Mapping

-- Show all extents for an object
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;

Free Space Analysis

-- Check if block is in free space
SELECT file_id, block_id, blocks,
       'FREE SPACE' object_type
FROM dba_free_space
WHERE file_id = &file_id
AND &block_id BETWEEN block_id AND (block_id + blocks - 1)

UNION ALL

SELECT e.file_id, e.block_id, e.blocks,
       o.object_type
FROM dba_extents e, dba_objects o
WHERE e.file_id = &file_id
AND &block_id BETWEEN e.block_id AND (e.block_id + e.blocks - 1)
AND o.owner = e.owner
AND o.object_name = e.segment_name
AND o.object_type = e.segment_type;

Corruption Recovery Scenarios

Table Corruption

If the block belongs to a table:

1. Assess Impact: Determine affected rows
2. Recovery Options: RMAN block recovery, table rebuild
3. Business Impact: Evaluate criticality and downtime

Index Corruption

If the block belongs to an index:

1. Drop and Rebuild: Often the simplest solution
2. RMAN Recovery: For critical unique indexes
3. Performance Impact: Consider during peak hours

System Object Corruption

If the block belongs to a system object:

1. Immediate Action: May require instance restart
2. Recovery Planning: Coordinate with Oracle Support
3. Impact Assessment: Could affect entire database

Block Recovery Commands

RMAN Block Recovery

-- RMAN block-level recovery
RMAN> RECOVER DATAFILE 4 BLOCK 12345;

Index Rebuild

-- For corrupted index blocks
ALTER INDEX hr.emp_name_idx REBUILD;

Table Analysis

-- Analyze table for corruption
ANALYZE TABLE hr.employees VALIDATE STRUCTURE CASCADE;

Prevention and Monitoring

Regular Validation

-- Check for new corruptions
SELECT file#, block#, blocks, corruption_type,
       corruption_change#, marked_corrupt
FROM v$database_block_corruption
WHERE marked_corrupt = 'TRUE';

Proactive Monitoring

-- Create monitoring for new corruptions
CREATE OR REPLACE VIEW corruption_monitor AS
SELECT file#, block#, corruption_type,
       TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') check_time
FROM v$database_block_corruption;

RMAN Validation Scripts

# Regular RMAN validation
rman target / <<EOF
VALIDATE DATABASE;
VALIDATE ARCHIVELOG ALL;
EOF

Integration with Recovery Tools

DBVerify Integration

# Verify specific file
dbv file=/path/to/datafile.dbf feedback=100 start=12340 end=12350

RMAN Integration

-- List corruptions found by RMAN
LIST CORRUPTION;

-- Restore and recover specific blocks
RESTORE DATAFILE 4 FROM TAG 'WEEKLY_BACKUP';
RECOVER DATAFILE 4 BLOCK 12345;

Best Practices

1. Immediate Response

   • Document the file# and block# from error messages
   • Run this script to identify affected objects
   • Assess business impact before planning recovery

2. Recovery Planning

   • Consider object criticality and size
   • Plan recovery during maintenance windows
   • Have rollback plans for recovery attempts

3. Preventive Measures

   • Regular RMAN validations
   • Monitor storage subsystem health
   • Maintain current backups

Troubleshooting

No Objects Found

If the script returns no rows:

• Free Space: Block might be in free space
• Incorrect Coordinates: Verify file# and block# values
• Dropped Objects: Object may have been dropped

Multiple Objects

If multiple objects appear:

• Overlapping Extents: Possible data dictionary corruption
• Partition Confusion: Check partition mapping carefully

Related Scripts

• File Information
• Extent Analysis
• Block Analysis