Moving tables with LOBS

[sreddy]

Anybody have any experiences moving tables with LOBS from one db to another ? Pre-import table creation and import with ignore=y seems do not work for me. I have tables with CLOBS to be moved.. (internal LOBS only)

*COPY command doesn't work, SQL*Plus crashed for me when I tried.
*CTAS doesn't work as there needs to dblink and LOBS are not supported over dblinks.

Is there any easy of doing it ? other than creating the table with LOB on target database and inserting the data with SQL*PLus inserts.

[aarroyob]

Sam,

Have you tryed the dbms_lob.copy? I have not tryed but i think it will work over a db link.

Cheers

Angel

[sreddy]

Originally posted by aarroyob
Have you tryed the dbms_lob.copy?

Nope! its not supported either in distributed environment ( I mean using dblink).

[aarroyob]

This is part of the note 119897.1 from metalink:

"In short, you cannot select a lob column using remote dblink. You have to use
DBMS_LOB package in a programming language like PL/SQL, JAVA to manipulate LOB
data."

But probably you have reason and the note is confussing

Cheers

Angel

[sreddy]

Originally posted by aarroyob

But probably you have reason and the note is confussing

TRUE ! Metalink Note#158924.1

$oerr ora 22992
22992, 00000, "cannot use LOB locators selected from remote tables"
// *Cause: A remote LOB column cannot be referenced.
// *Action: Remove references to LOBs in remote tables.