Forum Stats

  • 3,758,589 Users
  • 2,251,415 Discussions
  • 7,870,256 Comments

Discussions

How to convert DBA from DBVERIFY to file and block_id for bigfile

This is on 10.2.0.4 on HP-UX. I have DBVERIFY output like this on a bigfile data file:

DBV-00201: Block, DBA 18121156, marked corrupt for invalid redo application

I would like to query DBA_EXTENTS to find out what segment 18121156 is in. I know the file number because I have the datafile name from my dbv command. But how do I get the block_id?

Oracle's support site says that I cannot use dbms_utility.data_block_address_block for bigfiles but I cannot find the alternative.

Can anyone point me in the right direction?

Thanks!

Bobby

Best Answer

  • Bobby Durrett
    Bobby Durrett Member Posts: 140 Blue Ribbon
    edited Mar 3, 2021 5:59PM Accepted Answer

    It seems that the DBVERIFY (dbv) DBA value for bigfiles is the same as block_id within DBA_EXTENTS.

    So my query would be:

    SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents WHERE file_id = 29 and 18121156 between block_id AND block_id + blocks - 1;

    Bobby

Answers

  • Bobby Durrett
    Bobby Durrett Member Posts: 140 Blue Ribbon
    edited Mar 3, 2021 5:59PM Accepted Answer

    It seems that the DBVERIFY (dbv) DBA value for bigfiles is the same as block_id within DBA_EXTENTS.

    So my query would be:

    SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents WHERE file_id = 29 and 18121156 between block_id AND block_id + blocks - 1;

    Bobby