1

I am trying to create a automated way of copying data from a production oracle database to our test system. We only want to get specific data and so can't just do a full DB copy. I have all the SQL to get the data and can do it in toad by running the SQL and exporting as insert statements and then running those in the test system but this is time consuming as about 25 tables are involved.

I want to do it using python and cx_oracle. Can I have 2 connections to different databases open at the same time? So I can run a select in one and with the results build an insert and run that in the other or do I need to connect, run the select, disconnect and connect to the the other, run the insert and repeat for each statement? Would the multiple connects and disconnects cause issues for the databases? Taking down a production database will not make me popular....

I can't link the databases in any way and the copying needs to be repeatable for different data and simple enough for non technical users to use.

Improve this question
3
  • 1
    The answer to your question is yes, you can have multiple connections open at the same time. Commented Aug 9, 2013 at 8:24
  • Check out sql*Plus COPY command. could be very useful for you. Commented Aug 9, 2013 at 12:08
  • It will be slow. Why not consider exctract with SQLPlus and then load with SQLLoader. You can script it all using Python Commented May 20, 2016 at 14:49

1 Answer 1

Reset to default
2

Yes, you can have multiple connections open at the same time. Take care of each connection and cursor, of course.

You can find useful CX-OracleTools, a collection of scripts based on cx_oracle for working with Oracle databases. There is a CopyData script very interesting.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.