| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: From row to datafile
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
-- 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
 |
 |