RE: Block question

Let me try to explain, if I can. Here’s a snippet of select file_id, block_id, bytes, blocks from dba_extents:

   FILE_ID   BLOCK_ID      BYTES     BLOCKS

• ---------- ---------- ----------

         1        256      65536          8

         1        264      65536          8

         1        272      65536          8

         1        280      65536          8

         1        288      65536          8

To save space, block_id’s between 256 and 264 are not listed.

So if you got a report of datablock corruption in block 258, you can’t immediately tell what segment is in that block. But if you search for where 258 is between block_id and block_id +blocks -1, you’ll find it. 258 is between 256 and 263 (256+8-1).

Incidentally, the -1 ensures you are looking at a single block.

Does that help?

HYPERLINK "http://www.oracle.com/"Oracle | Micros

Stephan Uzzell |  Senior Database Administrator | HGBU Cloud Operations

Mobile: +1 410.227.6732

Oracle Hospitality

Swarthmore, PA | US

HYPERLINK "http://www.oracle.com/commitment"cid:image002.gif_at_01CFDD84.DCD06460

Oracle is committed to developing practices and products that help protect the environment

From: Paul Harrison [mailto:cure_at_austin.rr.com] Sent: Friday, 27 February, 2015 16:01
To: mark.brinsmead_at_gmail.com
Cc: ORACLE-L
Subject: RE: Block question

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: HYPERLINK "mailto:oracle-l-bounce_at_freelists.org"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: HYPERLINK "mailto:cure_at_austin.rr.com"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 <HYPERLINK "mailto:cure_at_austin.rr.com"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:13:24 CET