Re: Block question
- From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
- To: cure@xxxxxxxxxxxxx
- Date: Fri, 27 Feb 2015 13:11:30 -0800
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@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *MARK BRINSMEAD
> *Sent:* Friday, February 27, 2015 2:30 PM
> *To:* cure@xxxxxxxxxxxxx
> *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@xxxxxxxxxxxxx> 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
>
>
>
Other related posts:
