Migrating data from one table of one database to other table of other database
I am trying to migrate data from table A of database DB1 to table B of database DB2 using java and Oracle.
I am using java 1.8 and my source database has Oracle 11g and destination database has Oracle 12c.
I made structure (scema, tables )of destination database in source database. And migrating as by making use of *insert into dest select * from source* query in java . but as the number of records in source table in millions so it's consuming time.. and later on this migrated data i want to export into my actual destination so that too will going to take time.
As per my little knowledge.. i think I can't use prepared statement with 2 connection. Because my table consists of 400 to 500 columns , so binding that many columns with prepared statement is not a good idea. Also my structure of source and destination tables are different. I made the field mapping in properties file where I mapped the old field to new field for insert into select * from tbl query. Like my source table has column as col0001 and the corresponding column in destination is ref_no. So this too will not allow me to use prepared statement. But by making use of statement in java i can migrate data in single dB only.
I tried with dblink also. But for clob datatype i am not able to migrate data.
Kindly provide the solution if anyone did something like this previously.
For a one-off copy, you can do a direct mode insert:
insert /*+ APPEND */ into local_table select * from table@database_link;
Here are some other related links.