RE: From row to datafile
- From: Riyaj Shamsudeen <rshamsud@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 06 Aug 2004 10:08:37 -0500
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Scott
Sent: Thursday, August 05, 2004 5:20 PM
To: oracle-l@xxxxxxxxxxxxx
Cc: DWILLIAMS@xxxxxxxxxxxxx
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
--- DENNIS WILLIAMS <DWILLIAMS@xxxxxxxxxxxxx> wrote:
> 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@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
