Hi, Tom.
I have two DB instance on different server. One DB is main, second db is "archive". We need copy only data from first db to second which contains CLOB and BLOB info, whole table.
It's not copy all data from one to second. It's copy some data.
How can i do it fast and right?
What i need to do?
Have Oracle some instrument for this?
Do i need to use dblink or some thing alse?
A database link will do the trick
Database 1
==========
SQL> create table t ( pk int, c clob );
Table created.
SQL>
SQL> insert into t
2 select rownum,
3 rpad(rownum,32000) from dual
4 connect by level <= 100;
100 rows created.
SQL>
SQL> commit;
Commit complete.
Database 2
==========
SQL> create database link db connect to my_user identified by my_password using 'pdb122a';
Database link created.
SQL> create table t1 ( pk int, c clob );
Table created.
SQL>
SQL> insert into t1
2 select * from t@db;
100 rows created.
In my case, pdb122a is a tnsnames.ora entry that points to database 1.
Also, take care in terms of active transactions - you want to make sure you don't miss any uncommitted transactions when you run the insert. How to tackle this depends on your requirements and current application design.