• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

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

 
Ranch Hand
Posts: 186
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Sheriff
Posts: 28371
99
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 50
5
Oracle Postgres Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 50
5
Oracle Postgres Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  •  
    It is an experimental device that will make my mind that most powerful force on earth! More powerful than this tiny ad!
    Smokeless wood heat with a rocket mass heater
    https://woodheat.net
    reply
      Bookmark Topic Watch Topic
    • New Topic