I have a set of sql scripts which has to be run on a periodical basis say every two hours.
These scripts create tables which are populated from a remote database using db link and if and only if the process is completed without any problem, we truncate a set of different tables and populate with the data in the newly created tables and drop the newly created tables.
I want it to be part dbms scheduling but i have no idea on how to do this.
You may try to write a stored procedure instead of a script. In the procedure, do the following
1. Write a dynamic SQL 'Create temp table as select * from remote_table@remote_link' and then execute it.
2. Write a dynamic SQL 'Truncate local_table' and execute it.
3. Write a dynamic SQL 'INSERT INTO local_table select * from temp_table' and execute it.
Then create a scheduler job using DBMS_SCHEDUER.create_job to run the stored procedure at your preferred interval.