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@xxxxxxxxxxxxx
<mailto: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>
[mailto: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 <mailto: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
<mailto: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
