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;

Conclusion

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.