Query Data from Specific Block (qblock.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 provides comprehensive block-level analysis by:

• Step 1: Identifying which database object owns the specified block
• Step 2: Retrieving all rows stored in that specific block
• Using ROWID functions to precisely target the block contents
• Supporting partitioned objects with subobject identification
• Showing object metadata including creation and modification dates
• Providing detailed ROWID decomposition for each row

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' new_val owner
col object_name format a30 heading 'OBJECT NAME' new_val table
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' new_val data_object_no
col relative_fno format 999999 heading 'REL|FILE|NO' new_val rel_fno
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
set feedback on
rem
ttitle 'Rows from File: &fileid, Block &block_id'
rem
-- define data_object_no=3450
-- define rel_fno=411
-- define block_id=3202
define start_block_no=&block_id
define end_block_no=&block_id
define start_row_number=0
define end_row_number=9999
rem
define rowidtype=1
define object_no=&data_object_no
define relfno=&rel_fno
define block_no=&block_id
define startrow=&start_row_number
define endrow=&end_row_number
rem
col data_object_id format 9999999
col rel_fno format 9999
col block_id format 9999999
col row_no format 9999
rem
rem define table=FET$
select /*+ rowid(t) */
rowid,
dbms_rowid.rowid_object(rowid) data_object_id,
dbms_rowid.rowid_relative_fno(rowid) rel_fno,
dbms_rowid.rowid_block_number(rowid) block_id,
dbms_rowid.rowid_row_number(rowid) row_no,
t.*
from &owner..&table t
where rowid between
       dbms_rowid.ROWID_CREATE(&rowidtype, &object_no, &relfno, &start_block_no, &startrow)
and
       dbms_rowid.ROWID_CREATE(&rowidtype, &object_no, &relfno, &end_block_no, &endrow)
/

Usage

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

Parameters

• file_id: Absolute database file identifier (from V$DATAFILE)
• bad_block: Block number within the specified file

Required Privileges

• SELECT on SYS.DBA_OBJECTS
• SELECT on SYS.DBA_EXTENTS
• SELECT on the target table (determined dynamically)

Sample Output

Object Identification (Step 1)

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

Row Data Retrieval (Step 2)

Rows from File: 4, Block 12345

ROWID              DATA_OBJECT_ID REL_FNO BLOCK_ID ROW_NO EMPLOYEE_ID FIRST_NAME LAST_NAME
------------------ -------------- ------- -------- ------ ----------- ---------- ----------
AAAMcKAAEAAAADAAAa          12345       4    12345      0        1001 John       Smith
AAAMcKAAEAAAADAAAb          12345       4    12345      1        1002 Jane       Doe
AAAMcKAAEAAAADAABc          12345       4    12345      2        1003 Bob        Johnson

Key Output Information

Object Details

• OWNER: Schema that owns the object
• OBJECT NAME: Table, index, or segment name
• SUBOBJECT NAME: Partition name (if applicable)
• OBJECT TYPE: Type of database object
• DATA OBJECT ID: Internal Oracle identifier
• REL FILE NO: Relative file number within tablespace
• CREATED/MODIFIED: Object timestamps
• S: Status (I = Invalid, blank = Valid)

Row Details

• ROWID: Oracle internal row identifier
• DATA_OBJECT_ID: Object identifier from ROWID
• REL_FNO: Relative file number from ROWID
• BLOCK_ID: Block number from ROWID
• ROW_NO: Row slot number within block
• Additional Columns: All table columns and data

Common Use Cases

1. Corruption Analysis

   • Investigate ORA-01578 errors
   • Examine specific corrupt blocks
   • Identify affected data rows

2. Block-Level Troubleshooting

   • Analyze block contention issues
   • Investigate buffer busy waits
   • Examine hot block problems

3. Forensic Analysis

   • Trace specific data modifications
   • Investigate unexpected data changes
   • Support recovery planning

Finding File and Block Numbers

From Oracle Error Messages

ORA-01578: ORACLE data block corrupted (file # 4, block # 12345)
ORA-08102: index key not found, obj# 12345, file 4, block 12345

From Alert Log Queries

-- Search 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

-- Check corruption findings
SELECT file#, block#, blocks, corruption_type
FROM v$database_block_corruption
ORDER BY file#, block#;

From Performance Views

-- Find hot blocks
SELECT file#, block#, count
FROM (
    SELECT file#, block#, COUNT(*) count
    FROM v$bh
    WHERE status = 'xcur'
    GROUP BY file#, block#
    ORDER BY count DESC
)
WHERE ROWNUM <= 10;

Block Analysis Techniques

Multiple Block Range

To examine a range of blocks, modify the script:

-- Change these definitions
define start_block_no=12345
define end_block_no=12350

Specific Row Range

To examine specific rows within a block:

-- Change these definitions
define start_row_number=0
define end_row_number=10

ROWID Analysis

-- Decompose ROWID components
SELECT rowid,
       DBMS_ROWID.ROWID_OBJECT(rowid) object_id,
       DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) rel_file,
       DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) block_num,
       DBMS_ROWID.ROWID_ROW_NUMBER(rowid) row_num
FROM table_name
WHERE DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) = 12345;

Advanced Analysis

Block Utilization

-- Check block space usage
SELECT file_id, block_id,
       COUNT(*) rows_in_block,
       AVG(vsize(column_list)) avg_row_size
FROM (
    -- Results from qblock script
)
GROUP BY file_id, block_id;

Cross-Block Analysis

-- Find related blocks for an object
SELECT extent_id, file_id, block_id,
       block_id + blocks - 1 end_block,
       blocks
FROM dba_extents
WHERE owner = 'HR'
  AND segment_name = 'EMPLOYEES'
ORDER BY extent_id;

Corruption Recovery Scenarios

Table Block Corruption

1. Identify affected rows using this script
2. Assess data loss from row examination
3. Plan recovery strategy based on importance
4. Use RMAN block recovery or table rebuild

Index Block Corruption

1. Identify index type from object information
2. Drop and rebuild index (usually simplest)
3. Use RMAN recovery for critical unique indexes

Partition-Specific Issues

1. Identify affected partition from subobject name
2. Consider partition-level recovery
3. Plan minimal downtime approach

Recovery Commands

RMAN Block Recovery

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

Table Analysis

-- Validate table structure
ANALYZE TABLE hr.employees VALIDATE STRUCTURE CASCADE;

Index Rebuild

-- Rebuild affected index
ALTER INDEX hr.emp_name_idx REBUILD;

Monitoring and Prevention

Proactive Block Monitoring

-- Create monitoring view
CREATE OR REPLACE VIEW hot_blocks AS
SELECT file#, block#, count(*) access_count,
       MAX(timestamp) last_access
FROM v$bh
GROUP BY file#, block#
HAVING COUNT(*) > 10;

Regular Validation

# RMAN validation schedule
rman target / <<EOF
VALIDATE DATABASE;
LIST CORRUPTION;
EOF

Integration with Other Tools

DBVerify Integration

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

ASH Integration

-- Find sessions accessing this block
SELECT session_id, session_serial#, sql_id, event
FROM dba_hist_active_sess_history
WHERE current_file# = 4
  AND current_block# = 12345
  AND sample_time > SYSDATE - 1;

Best Practices

1. Immediate Response

   • Document file# and block# from errors
   • Run this script to assess impact
   • Plan recovery during maintenance window

2. Data Preservation

   • Export good data before recovery attempts
   • Document affected rows
   • Plan rollback strategies

3. Root Cause Analysis

   • Check storage subsystem logs
   • Review recent changes
   • Examine workload patterns

Troubleshooting

No Object Found

Possible Causes:

• Block is in free space
• Incorrect file# or block# values
• Object has been dropped
• System/temporary space

Solutions:

• Verify coordinates from error messages
• Check DBA_FREE_SPACE for free blocks
• Review alert log for context

Permission Errors

Possible Causes:

• Insufficient privileges on target table
• Object in restricted schema

Solutions:

• Grant SELECT privileges
• Run as SYSDBA if needed
• Check object accessibility

Related Scripts

• Database Object from Block ID
• Find Block Segment Information
• Block Analysis
• File Information