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: Conversion from LONG -> CLOB -> BLOB for 1 TB Table?

RE: Conversion from LONG -> CLOB -> BLOB for 1 TB Table?

From: Nuno Souto <dbvision_at_iinet.net.au>
Date: Thu, 18 Jan 2007 08:43:16 +1100
Message-ID: <1169070196.45ae9874b6738@mail.iinet.net.au>


Quoting VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>:

>
> For a 1 TB Table, Can Conversion be Done ONLINE from LONG to BLOB using
> DBMS_REDEFINITION (shown below):-

never tried online, as these things require a lot of work and it might be avisable to do this piecemeal for large sizes. My preferred path for 9i would be to use CTAS with this http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions139a.htm#79466 as the converter:
the SQL native function TO_LOB works quite well and is fast, being internalized.

Suspect that's what DBMS_REDEFINITION would use behind the scenes anyway.

> Will Conversion from LONG to an intermediate CLOB result in Corruption
> of the Existing JPEGs Binary Data?

I'm afraid so, yes. CLOB implicitly tries to apply NLS processing to its contents, whatever the NLS settings might be. That'd be a no-no with jpg data...

> Any Other Advisable Ways to Convert with Minimal Down Time?
>

I think you mentioned targets being 9i and 10g. Be sure to check if what you want to do with 10g works as well for 9i. There is more functionality in 10g in DBMS_LOB and DBMS_REDEFINITION but that is no help if your target is 9i.

-- 
Cheers
Nuno Souto
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jan 17 2007 - 15:43:16 CST

Original text of this message

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