how to replicate a BLOB field to another DB table
Geert van de Leur, July 17, 2002 - 10:12 am UTC
It takes some time, but it works fine. I was trying to import a schema's objects into a database running on an Alpha/VMS system and repeatedly got kicked out because of insufficient process quota. As only one table (1.9 million rows, one blob column and one clob column) caused the problem, I created the (empty) table and copied the data from the source table. Using this method I was able to do this in chunks of 100,000 rows. Yoy have to have time and patience, though ;)
July 17, 2002 - 1:55 pm UTC
you could have set the blob/clob to nologging, the table to nologging and did a
insert /*+ APPEND */ into t select * from another_t
(making sure there are no indexes on T) and bypass redo/undo. then put them back to logging and backup the tablespaces you loaded into. That'll speed it up (use PARALLEL even). If you have my book -- I do have a full example of that (actually, it is a conversion from long raw to blob but the concepts are identical -- speed up a mass copy of really big stuff)
A reader, March 21, 2005 - 12:28 pm UTC
Why does an
Insert into Tab1 (at DB2)
select from Tabl1@DB1 ;
work but a Materialized View at DB2 does not?
The materialized view is
select col1, col2, Col3_CLOB
from Tab1@DB1 ;
March 22, 2005 - 10:54 am UTC
why won't my car start?
we have about the same amount of information :)
error message/symptons/ throw me a bone...
A reader, March 24, 2005 - 6:45 am UTC
My apologies. That's what I get for being in hurry.
Oracle version on both DB1 and DB2 is 9.2.0.4
On DB1 the table is TAB1 that has a clob.
DESC TAB1
Name Null? Type
----------- -------- ----------
DOCID NOT NULL VARCHAR2(2)
DOCNO NOT NULL VARCHAR2(10)
SHPDT NOT NULL DATE
APPCD VARCHAR2(1)
LOGCD VARCHAR2(5)
DOC_NAR CLOB
From DB2 the query
SQL> select * from tab1@DB1 ;
select * from tab1@DB1
*
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables
But if I
SQL> create table tab1_test as (select * from tab1@DB1);
Table created.
Can you explain why the error doing the Select, but not doing the Create Table as Select
March 24, 2005 - 8:59 am UTC
because the create table as select copies data over, not the lob locator.
see the original example - copy the data over using insert, then you can use a local lob locator.
Why the need for a 3rd DB?
Rich, March 24, 2005 - 10:05 am UTC
Hi Tom,
Going back to your reply to the original question,
why do we need a 3rd DB to move the BLOB from DB1 to DB2?
Why won't a straight INSERT from DB2 to DB1 work?
Thanks a lot.
March 24, 2005 - 3:56 pm UTC
good question -- you need only two. i wonder what I was thinking that day.
you can obviously do it with two -- for I did (copied from a to b, b to c -- a to b would have done it)