Recently, I had to import data from one Oracle database into another. I also needed to schedule a job to do this on a recurring basis. Here are the steps I undertook to accomplish this -
Step 1 - Setup a Database link to the Source Database in the Target Database schema
Since both the Source & Target database were Remote databases, I ran into some issues with defining the Service Names.
I found the following links helpful -
http://dba.stackexchange.com/questions/18408/move-data-from-one-oracle-db-to-another-db-using-toad
https://www.youtube.com/watch?v=90Jmmc3DvWQ
http://msutic.blogspot.in/2009/07/how-to-create-database-link-without.html
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9532217300346683472
Step 2 - Create a Stored Procedure with the relevant SQL code
I did so using Oracle SQL Developer
Useful Links -
https://www.youtube.com/watch?v=buaSuEMi4lw
(View Parts 1 & 2)
Step 3 - Create a Database Schedule & an associated Database Job
Oracle DBMS_Scheduler package allows segregation between Programs, Schedules & Jobs. Since, I already had the relevant SQL code compiled as a Stored Procedure, I just had to create a Database Schedule & then an associated Database Job to run that Stored Procedure as per the defined Schedule
Useful Links -
http://allthingsoracle.com/introduction-to-scheduled-jobs/
https://www.youtube.com/watch?v=detNIFuhOGo
