This content has been marked as final.
Show 2 replies
-
1. Re: How to do dbms scheduling?
user503635 Dec 28, 2012 12:54 PM (in response to 998899-Oracle)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.
4. Commit
Then create a scheduler job using DBMS_SCHEDUER.create_job to run the stored procedure at your preferred interval.
DECLARE
BEGIN
dbms_scheduler.create_job(
job_name=>'my_proc',
job_type => 'stored_procedure',
job_action=> 'my_procedure',
repeat_interval => 'FREQ=HOURLY; INTERVAL=2',
enabled => true,
auto_drop=> false
);
END; -
2. Re: How to do dbms scheduling?
Mihael Dec 29, 2012 11:28 AM (in response to 998899-Oracle)To simplify and optimize the procedure, you can create materialized views and then just refresh them when you need.