WHERE clause within SQL statement for DBVerify

[Former Member]

Hi!

After a DBVerify job that runs with errors I would like to identify all the tables/idexes affected.

The first courrupted block is 286215 and the last 286725.

Question:

How can I adapt the following SQL statement for block iterval 286215 - 286725 within WHERE clause?

SQL> select segment_name, partition_name, segment_type, block_id, blocks

2 from dba_extents

3 where (286215 between

+4 block_id and (block_id + blocks - 1))+

5 and file_id = 11 and rownum < 2;

Thank you!

[lbreddemann]

Hi there,

just to make things easier - try RMAN for block validation.

See my blog on that: [Some hints to make handling of oracle datablock corruptions more efficient|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/9285] [original link is broken] [original link is broken] [original link is broken];.

regards,

Lars

[Former Member]

Shouldn't be that difficult, using a bit of understanding of Oracle concepts and very little elementary mathematics.

But on the other hand: Do you really need to do so?

You are looking for extents (you do know this term?) that are at least partly in the interval 286215 - 286725.

There are a lot of ways how to achieve this. Your current query may or may not be used as a starting point. And by the way, the condition rownum < 2 will restrict your result set to one value, whereas finally you might be expecting more.

You know how to interpret your current query?

It will tell you whether there is an extent around block 286215.

Well, now you could look for a query for extents around any block from 286215 to 286725. But why should you?

It's sufficient to look for extents that are beginning in your interval, because you already know if there is an extent that covers the left end of the interval, don't you?

And this query is left as an exercise to the reader.

A hint: The beginning is block_id.

hope this helps

[Former Member]

Hi Joe!

Many thanks for your lesson

Can you kindly answer my question by posting the appropriate SQL commando?

Thank you!

[Former Member]

What is your guess?