0

I want to Migrate data from an oracle database to another oracle database using python

# first database connection 
con1 = cx_Oracle.connect(username, password, host/service)
cursor1 = con1.cursor()    

# second database connection 
con2 = cx_Oracle.connect(username, password, host/service)
cursor2 = con1.cursor()                                  
sql = "insert into db2.table select * from db2.table"
Jamiu S.
  • 5,257
  • 5
  • 12
  • 34
hey kay
  • 5
  • 5

1 Answers1

1

There are two approaches to this.

1. DB link

You want to

TRUNCATE db2.tbl;

INSERT INTO db2.tbl  SELECT *  FROM db1.tbl;

You don't need a pair of cursors, a pair of open DB connections, to do that. A single connection to db2 suffices. What you do need is a DB link, so that db2 queries enjoy access to db1 tables. The syntax is:

CREATE DATABASE LINK dblink_name
    CONNECT TO remote_user_name IDENTIFIED BY password
    USING 'remote_database_name/connection_string';

So if you called it link_db1, you would transfer data with

INSERT INTO tbl  SELECT *  FROM link_db1.tbl;

After your client sends the command to db2, the client is out of the picture. There could be a million result rows, and they all flow directly from the db1 server to the db2 server. The client doesn't touch a single row. If client suffers from low network bandwidth, that won't impact the throughput of copying lots of row data.

https://docs.oracle.com/cd/E18283_01/server.112/e17120/ds_concepts002.htm

How to create a DB link between two oracle instances

2. in-memory copy

You can create a pair of connections if you want, one to each database.

Download the rows from one, and insert them via the other connection.

You can do it in a single batch if the client has sufficient memory, or you might prefer to do batches of a few thousand rows at a time. (Don't do a single row at a time, as it will be latency dominated -- the throughput will be disappointingly low.) Network bandwidth available to the client will play a big role in how quickly the rows can be copied.

The read_sql / to_sql functions offer one way to accomplish the copying.

This is the less attractive option. Prefer using a DB link, if feasible.

J_H
  • 17,926
  • 4
  • 24
  • 44
  • Some notes: If option 2 is used (despite being less efficient), make sure to tune `arraysize` when fetching, and use `executemany()` to do the insert in batches. Also, its time to upgrade to the new version of cx_Oracle (now called python-oracled), see the [release announcement](https://cjones-oracle.medium.com/open-source-python-thin-driver-for-oracle-database-e82aac7ecf5a). The new 'Thin' mode will almost certainly be faster for this kind of data transfer. – Christopher Jones Oct 09 '22 at 22:30