Re: Block question

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Fri, 27 Feb 2015 13:11:30 -0800
Message-ID: <CAA2Dszz0gXp=sB8AkvpuXGgJMKVKVvd0XkMSHae5c0AY1HEOkg_at_mail.gmail.com>



Not answering your question directly, but, "&block_value BETWEEN block_id AND ( block_id + blocks )" is incorrect. It should be

"&block_value BETWEEN block_id AND ( block_id + blocks -1) ". Notice the "-1" part.

Cheers

Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com - Specialists in Performance, RAC and EBS
Blog: http://orainternals.wordpress.com/ Oracle ACE Director and OakTable member <http://www.oaktable.com/>

Co-author of the books: Expert Oracle Practices <http://tinyurl.com/book-expert-oracle-practices/>, Pro Oracle SQL, <http://tinyurl.com/ahpvms8> <http://tinyurl.com/ahpvms8>Expert RAC Practices 12c. <http://tinyurl.com/expert-rac-12c> Expert PL/SQL practices <http://tinyurl.com/book-expert-plsql-practices>

<http://tinyurl.com/book-expert-plsql-practices>

On Fri, Feb 27, 2015 at 1:00 PM, Paul Harrison <cure_at_austin.rr.com> wrote:

> Hi Mark,
>
>
>
> *&block_value = *159440
>
>
>
> Why would I need the below as the other block #s within the extent(0) are
> not included in the the dba_extents view?
>
> 159440 BETWEEN 159440AND (159440 + 8)
>
>
>
>
>
> segment_name extent_id file_id block_id bytes
> blocks
>
> CUST 0 1 159440
> 65536 8
>
> CUST 1 1 159464
> 65536 8
>
>
>
>
>
>
>
> Thank You,
>
> Paul
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *MARK BRINSMEAD
> *Sent:* Friday, February 27, 2015 2:30 PM
> *To:* cure_at_austin.rr.com
> *Cc:* ORACLE-L
> *Subject:* Re: Block question
>
>
>
> No.
>
> The purpose of this query is (appears to be) to identify the segment (or
> the extent of the segment) containing a particular block. This is
> something you would probable want to do, for example, when your alertlog
> reports that block #12345 in file #7 has been corrupted.
>
>
>
> In order for *&block_value = blockid* to work (with any reliability),
> DBA_EXTENTS would need have a row for each and every block in each and
> every extent in the entire database. Not only would that not be proper
> normalization of the data, it would be incredibly wasteful of storage.
>
> DBA_EXTENTS reports only the first block and the number of blocks in the
> extent (recall that extents are always a contiguous range of blocks within
> a single datafile). To find the extent (or object) containing a block that
> interests you, you need check whether it is within a range.
>
>
>
> On Fri, Feb 27, 2015 at 3:21 PM, Paul Harrison <cure_at_austin.rr.com> wrote:
>
> Hi Team,
>
>
>
>
>
> I came upon the below on the internet.
>
> " ( &block_value BETWEEN block_id AND ( block_id + blocks ) )" <-- I
> don't understand how this comes into the equation. Shouldn't it just be
> &block_value = <value> ?
>
>
>
>
>
>
>
>
>
> List an Object and Object Type via a Given File and Block ID.
>
>
>
> --
>
> -- List an Object and Object Type via a Given File and Block ID.
>
> --
>
> SET PAUSE ON
>
> SET PAUSE 'Press Return to Continue'
>
> SET PAGESIZE 60
>
> SET LINESIZE 300
>
> COLUMN segment_name FORMAT A24
>
> COLUMN segment_type FORMAT A24
>
> SELECT segment_name, segment_type, block_id, blocks
>
> FROM dba_extents
>
> WHERE
>
> file_id = &file_no
>
> AND
>
> ( &block_value BETWEEN block_id AND ( block_id + blocks ) )
>
> /
>
>
>
>
>
>
>
> Thanks,
>
> Paul
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Feb 27 2015 - 22:11:30 CET

Original text of this message