Copy data from one database to another in Oracle

Question

I have 2 Oracle databases and I frequently copy data from prod DB to test DB using TOAD, by generating insert scripts for Prod DB and running it on the test DB later.

I am trying to do it faster through a batch file.

I think that I can use this solution but the DB has an auto-increment column. If I use this solution, would that column be affected? Do I need to change the script in some way? I haven't tried this so far as I have no access do the DB and would be able to test this only on Monday.

Is there a better way I can accomplish this? What I am essentially looking for is doing the whole copy procedure using a batch file, which would minimize the time I spend doing this using TOAD.

Also, it's fine by me if I can be guided in the right direction, if solution is not simple.

Answer 1

Make sure the two tables have the same structure.

Connect to the target database.

Create a public link to the source database. The user should have "CREATE PUBLIC DATABASE LINK" system privilege to do this.

CREATE PUBLIC DATABASE LINK mylink
  CONNECT TO source_user IDENTIFIED BY source_password
  USING 'source_entry_in_tnsnames';

Copy the data:

INSERT INTO mytable SELECT * FROM mytable@mylink;

If the primary key of the table comes from a sequence, set the sequence to - at least - the same value as in the source database:

ALTER SEQUENCE mysequence increment by 100000;
SELECT mysequence.nextval FROM DUAL;
ALTER SEQUENCE mysequence increment by 1;