Re: Block question
Date: Sat, 28 Feb 2015 13:10:42 -0500
Message-ID: <54F204A2.8030702_at_yahoo.com>
On 02/27/2015 04:11 PM, Riyaj Shamsudeen wrote:
> 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.
Of course. Counting starts with 0. The first block of a segment is block_id + 0 and last one is block_id + (blocks-1). Whoever has done a bit of C knows that. I would advise everybody to read Kernighan & Ritchie, even if it's not directly related to Oracle. Oracle is written in C and the spirit of the language is visible.
>
> Cheers
>
> Riyaj Shamsudeen
> Principal DBA,
> Ora!nternals - http://www.orainternals.com
> <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
> <mailto: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>
> [mailto: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 <mailto: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
> <mailto: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
>
>
-- Mladen Gogala Oracle DBA http://mgogala.freehostia.com -- http://www.freelists.org/webpage/oracle-lReceived on Sat Feb 28 2015 - 19:10:42 CET