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: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Fri, 6 Aug 2004 15:05:26 -0500
Message-ID: <0186754BC82DD511B5C600B0D0AAC4D60E9AE6C1@EXCHMN3>


Thanks Riyaj. Here was the query I ran:

SQL> select index_name from dba_lobs where owner = 'WIMG_OWN';

INDEX_NAME



SYS_IL0000027559C00007$$ SQL> select segment_name, file_id, block_id   2 from dba_extents where segment_name like 'SYS_IL0000027559C00007$$';

SEGMENT_NAME



   FILE_ID BLOCK_ID
---------- ----------
SYS_IL0000027559C00007$$
        20 8197

So I dump that block, but the trace file doesn't look like the blocks I dumped before. So you are saying that is just the header?

  0:Metadata 1:Metadata 2:Metadata 3:Metadata    4:Metadata 5:Metadata 6:Metadata 7:Metadata    8:Metadata 9:Metadata 10:Metadata 11:Metadata

   12:Metadata   13:Metadata   14:Metadata   15:Metadata
   16:Metadata   17:Metadata   18:Metadata   19:Metadata
   20:Metadata   21:Metadata   22:Metadata   23:Metadata
   24:Metadata   25:Metadata   26:Metadata   27:Metadata
   28:Metadata   29:Metadata   30:Metadata   31:Metadata
   32:Metadata   33:Metadata   34:FULL   35:FULL
   36:FULL   37:FULL   38:FULL   39:FULL
   40:FULL   41:FULL   42:FULL   43:FULL
   44:FULL   45:FULL   46:FULL   47:FULL
   48:FULL   49:FULL   50:FULL   51:FULL
   52:FULL   53:FULL   54:FULL   55:FULL
   56:FULL   57:FULL   58:FULL   59:FULL

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Riyaj Shamsudeen Sent: Friday, August 06, 2004 3:05 PM
To: oracle-l_at_freelists.org
Subject: RE: From row to datafile

Dennis

        Find the lob index name from user_lobs. Find the file_id, block_id and blocks associated with the lob index using dba_segments. Dump the blocks in those extents using alter system dump command. Realize that if you have huge lob tables, then your lob index might span many extents too. So, dumping all the blocks in that segment might create a huge trace file.
Hope that helps,
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 DENNIS WILLIAMS Sent: Friday, August 06, 2004 2:53 PM
To: oracle-l_at_freelists.org
Subject: RE: From row to datafile

Thanks Scott and Riyaj. Now a really simple stupid question - How can I dump the lob index??

I can find the index through the dba_lobs column. But I'm wandering around in the dark here trying to figure out how to dump the index. Can anyone help me? Thanks.

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

-----Original Message-----
From: Scott [mailto:oraracdba_at_yahoo.com] Sent: Thursday, August 05, 2004 5:20 PM
To: oracle-l_at_freelists.org
Cc: DENNIS WILLIAMS
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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 - 15:05:18 CDT

Original text of this message

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