Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: From row to datafile

RE: From row to datafile

From: Riyaj Shamsudeen <rshamsud_at_jcpenney.com>
Date: Fri, 06 Aug 2004 10:08:37 -0500
Message-id: <002401c47bc7$40337900$212f200a@rshamsudxp>


Of course, Scott is correct..I was wrong.. Every CLOB I have is over the threshold and stored out of line. I ASSumed that this is out of line LOB (after dumping the blocks), without looking at user_lobs first. If the CLOB is declared inline but stored out of line, then the pointer to lob data is 40 bytes instead of 20 bytes. Even then, first 20 bytes still matches whether the lob is declared inline or out-of-the-line as long as it is stored out-of-line. This worked to my advantage as I was trying to recreate an IO issue.

Dennis: Here is the example after testing it out..

Block dump of the data block:

block_row_dump:
tab 0, row 0, @0x1f9d
tl: 27 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [20] 00 54 00 01 02 08 00 00 00 01 00 00 00 01 00 00 04 0e fa 85

Here is the lobid : 00 00 00 01 00 00 04 0e fa 85 Dump the lob index..Search for the above key..col 0: is the lob id.

row#0[7982] flag: -----, lock: 2, data:(32):  00 20 03 00 00 00 00 00 01 90 00 00 00 00 00 01 0a d6 18 22 00 00 00 00 00 00 00 00 00 00 00 00 col 0; len 10; (10): 00 00 00 01 00 00 04 0e fa 85 col 1; len 4; (4): 00 00 00 00

Look at the data associated with this lob id. 17th- 20th bytes: 0ad61822 is the DBA. Now you can convert this decimal and use the script to find the file/block..

I learned something new today, Thanks to eminent Scott..

HTH Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Scott Sent: Thursday, August 05, 2004 5:20 PM
To: oracle-l_at_freelists.org
Cc: DWILLIAMS_at_LIFETOUCH.COM
Subject: RE: From row to datafile

Dennis, The last 10 bytes 00 00 00 01 00 00 00 01 00 00 00 0c 08 3b is the lobid and the key into the lob index. This is not a rdba or a dba so that is why your query won't work. You will have to dump the lob index to retrieve the rdba where the actual lob is located. You will need will need to look through the lob index dump for column value that contains the lobid and above that value you will see a another 32 byte value and the 17th-20th bytes are the RDBA for the LOB segment.

Hope this helps,

Scott

> Thanks everyone, especially Riyaj and Ian. I really appreciate the
> hard work.
>
> Riyaj
> I tried to follow your method and seemed to be
> successful until I got
> down to the last step. I found the block and record
> of the main table. I
> dumped the block of the main table and got the
> following for the 35th record
> in the trace file:
>
> tab 0, row 35, @0x3219
> tl: 96 fb: --H-FL-- lb: 0x0 cc: 9
> col 0: [ 4] c3 4b 5c 0b
> col 1: [ 4] 6b 65 79 31
> col 2: [32]
> 64 38 37 66 33 36 61 30 64 66 32 33 31 38 37 31 35
> 61 39 65 38 66 34 62 35
> 37 65 62 35 62 37 32
> col 3: [ 3] 4d 44 35
> col 4: [ 3] c2 04 19
> col 5: [ 3] c2 03 35
> col 6: [20] 00 54 00 01 01 08 00 00 00 01 00 00 00
> 01 00 00 00 0c 08 3b
> col 7: [ 7] 78 68 08 05 0c 1e 33
> col 8: [ 8] 55 50 44 5f 55 53 45 52
> tab 0, row 36, @0x31b9
>
> However, when I convert c083b to decimal I get
> 788539. I plug that into your
> SQL query for the dba and recieve for file number .
> . . . 0! Can you spot
> where I've gotten off the path? Again, thanks for
> all your help.
>
> Dennis Williams
> DBA
> Lifetouch, Inc.
>
> "We all want progress, but if you're on the wrong
> road, progress means
> doing an about-turn and walking back to the right
> road; in that case,
> the man who turns back soonest is the most
> progressive."
> -- C.S. Lewis
                



Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------



-- Attached file included as plaintext by Ecartis --
-- Desc: Signature

The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material.  If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited.   If you are not the intended
recipient, please contact the sender and delete the material from any
computer.


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Fri Aug 06 2004 - 10:05:23 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US