6. Create a Job Schedule
Finally, I need to schedule the chain to run periodically. My job chain is scheduled to run every 2 weeks, on Sundays, at 9:00 PM; see Listing 11 which displays the procedure DBMS_SCHEDULER.CREATE_JOB.
BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB ( job_name => 'REFRESH_TEST.REFRESH_TEST_DATA' ,start_date => TO_TIMESTAMP_TZ('2010/05/15 00:00:00.000000 US/Eastern','yyyy/mm/dd hh24:mi:ss.ff tzr') ,repeat_interval => 'FREQ=WEEKLY;INTERVAL=2;BYDAY=SUN;BYHOUR=21;BYMINUTE=0;BYSECOND=0' ,end_date => NULL ,job_class => 'DEFAULT_JOB_CLASS' ,job_type => 'PLSQL_BLOCK' ,job_action => 'begin SYS.DBMS_SCHEDULER.RUN_CHAIN ( chain_name => ''REFRESH_TEST.REFRESH_PROC'' ,start_steps => null ); end;' ,comments => 'Refrseh Test Data from Prod' );
You can also run this chain manually by running the following script:
begin SYS.DBMS_SCHEDULER.RUN_CHAIN ( chain_name => 'REFRESH_TEST.REFRESH_PROC' ,start_steps => null ); end;
This automated process has saved me a lot of time and trouble. The process takes about 2 hours to run. Now I can have testers test new changes to my applications using a fresh Test database, since I run it periodically. For more detailed information on creating and managing job chains, go here.