Re: COPY table with LOBs

Dave Bender wrote:
>
> How can I easily copy a table and its data from one user schema to another
> when the table contains one or more LOB columms?
>
> I've tried the SQL*Plus "COPY" command and this works fine for all the other
> tables we need to copy. But if the table contains a LOB, I get this error:
>
> CPY0012: Object datatypes cannot be copied
>
> I've searched high and low for anything about the specific error (CPY0012),
> but haven't had much luck. However, the error message combined with the
> table description seems to point to the LOBs as the culprit. If that's the
> problem, how do I move the LOBs (ideally without having to write a program
> to do it)? We'd like this to happen on the Oracle server and not have to
> round-trip the LOB-ular data back and forth, since it's rather large.
>
> Thanks in advance.
>
> Dave

Have you tried the CTAS method (CREATE TABLE AS SELECT)?

Do something similar to the following:

CREATE TABLE new_user.new_table
AS SELECT * FROM old_user.old_table;

It should copy LOB columns as well.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"