Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to copy a table from one database to another database using JDBC?

 
sudha swami
Ranch Hand
Posts: 186
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I want to copy a table from one oracle database to another using Java (JDBC APIs). The two databases are disconnected from each other (i,e) they do not have any DBlinks pointing to each other.These tables will be having anywhere between 15,000 to 20,000 rows with 15 columns. Can someone specify the most efficient way to do this?

Thanks
Sudha
 
Paul Clapham
Sheriff
Posts: 21576
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, first you say you want to do this copying using Java. And then you say you're looking for the most efficient way to do it. There's a contradiction there, since the most efficient way would be to use Oracle tools to copy the data directly without involving any Java code at all. So I'd suggest you should review your requirements. Java? Efficient? Which is more important?
 
sudha swami
Ranch Hand
Posts: 186
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Paul,

Thanks for your reply.The requirement is copy a table from one database to another database on daily basis.
I want an efficient way. I will not go with Java. Currently, we have an Oracle 11g database. What are the oracle tools I should use?

Thanks
Sudha
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Talk to your DBA.

Probably the most straightforward way is to set up an Oracle database link between the two database schemas, plus appropriate GRANTs and synonyms on the relevant tables. Then you can just use SQL to move the data from either end whenever you want. Watch out for integrity constraints e.g. you need to make sure the parent records exist before you insert the children to the target database, and any primary keys must be unique of course. You can switch constraints off while you're loading data then re-enable them afterwards, but that's risky if other applications are writing to the same tables, and it's probably not worth doing for these small volumes of data. You will need to keep an eye on the sizing for your target system, however, as it sounds it will be accumulating data fairly rapidly. Maybe look at partitioning the tables, e.g. by date, which can also help to speed up the transfer and improve management of the data. Again, you need to talk to an experienced Oracle DBA for proper advice on this stuff.

Another option would be to use the PL/SQL API for Oracle's DataPump bulk data-loader which allows you to export data programmatically to flat files (in an Oracle-specific format) and import them into another database. Your DBA will need to advise on this e.g. you'll need access to appropriate directories on the DB server etc. DataPump is very fast, but probably a bit too complicated for your needs.

But plain old SQL is probably easier for what you want to do, and for the small volumes of data SQL you're moving every day SQL should be plenty fast enough.

Either of the above options could be coded as PL/SQL packaged procedures that could then be scheduled easily e.g. to run overnight. You can even use the Oracle scheduler in the database to do this so you don't need any Java at all here.
 
Lukas Eder
Ranch Hand
Posts: 49
5
Java Oracle Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you're only copying some 20k records between databases on a daily basis, I don't see a huge performance issue coming at you when using JDBC or CSV or anything similar. In E-Banking applications, we've unloaded / loaded millions of records per day through a proprietary CSV text format.

One hint that I can give you, though, is to use a dedicated loading table in the target database. I.e. a table without indexes or constraints that is used just for a single load. Once you've loaded your CSV data into that table, you can then use much more efficient PL/SQL (or T-SQL or whatever) stored procedures to transfer / merge that data with the actual target. After the transfer, you'll truncate the loading table again.
 
Lukas Eder
Ranch Hand
Posts: 49
5
Java Oracle Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
sudha swami wrote:Can someone specify the most efficient way to do this?


Note, if by "most efficient" you mean "most easy to implement", then I would recommend you evaluate jOOQ for that task. jOOQ easily exports to / imports from CSV format. Something along these lines might do:



See the manual for details about:

  • exporting to CSV
  • importing from CSV
  •  
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic