dbms_scheduler package系列(七)-1

本文繼續學習dbms_scheduler包的其它用法,是為dbms_scheduler package系列七

 建立job幾種方式:
   Creating Jobs Using a Named Program
 

   Creating Jobs Using a Named Schedule
 

   Creating Jobs Using a Named Program and Schedule
  
用指定的program建立job 
Creating Jobs Using a Named Program  
 1,不要指定job_type,job_action,number_of_arguments引數值
 
---建立program
SQL> begin
  2  dbms_scheduler.create_program(program_name => 'program_1',program_type => 'stored_procedure',program_action => 'proc_arg',enabled => true,comments => 'program 1 comments');
  3  end;
  4  /
 
PL/SQL procedure successfully completed

SQL> begin
  2  dbms_scheduler.create_job(job_name => 'job_program',job_type => 'stored_procedure',job_action => 'proc_arg',program_name=>'program_1');
  3  end;
  4  /
 
begin
dbms_scheduler.create_job(job_name => 'job_program',job_type => 'stored_procedure',job_action => 'proc_arg',program_name=>'program_1');
end;
 
ORA-06550: line 3, column 1:
PLS-00306: wrong number or types of arguments in call to 'CREATE_JOB'
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored

--移除jog type及job action引數後正確執行
SQL> begin
  2  dbms_scheduler.create_job(job_name => 'job_program',program_name=>'program_1');
  3  end;
  4  /
 
PL/SQL procedure successfully completed

 

 

用指定的schedule建立job
Creating Jobs Using a Named Schedule

不要指定start_date,repeat_interval,end_date引數值

---引用之前建立的schedule,注意:已含有start_date,repeat_interval,end_date引數值,故不用在create_job再次指定其相應的值
SQL> select * from user_scheduler_schedules;
 
SCHEDULE_NAME                  SCHEDULE_TYPE START_DATE                                                                       REPEAT_INTERVAL                                                                  EVENT_QUEUE_OWNER              EVENT_QUEUE_NAME               EVENT_QUEUE_AGENT              EVENT_CONDITION                                                                  FILE_WATCHER_OWNER                                                               FILE_WATCHER_NAME                                                                END_DATE                                                                         COMMENTS
------------------------------ ------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
MY_SCHEDULE                    CALENDAR      31-JAN-13 11.03.49.798000 AM +08:00                                              FREQ=DAILY;BYHOUR=12,13                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           my_schedule_comment
 
 
---為start_date指定引數值報錯
SQL> begin
  2  dbms_scheduler.create_job(job_name=>'job_schedule',job_action=>'proc_arg',job_type=>'stored_procedure',schedule_name=>'my_schedule',start_date => sysdate);
  3  end;
  4  /
 
begin
dbms_scheduler.create_job(job_name=>'job_schedule',job_action=>'proc_arg',job_type=>'stored_procedure',schedule_name=>'my_schedule',start_date => sysdate);
end;
 
ORA-06550: line 3, column 1:
PLS-00306: wrong number or types of arguments in call to 'CREATE_JOB'
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored

----修正移除start_date正確
SQL> begin
  2  dbms_scheduler.create_job(job_name=>'job_schedule',job_action=>'proc_arg',job_type=>'stored_procedure',schedule_name=>'my_schedule');
  3  end;
  4  /
 
PL/SQL procedure successfully completed

 

 

 

使用指定的program和schedule建立job
Creating Jobs Using a Named Program and Schedule

SQL> select * from user_scheduler_programs;
 
PROGRAM_NAME                   PROGRAM_TYPE     PROGRAM_ACTION                                                                   NUMBER_OF_ARGUMENTS ENABLED DETACHED SCHEDULE_LIMIT                                                                    PRIORITY     WEIGHT   MAX_RUNS MAX_FAILURES MAX_RUN_DURATION                                                                NLS_ENV                                                                          COMMENTS
------------------------------ ---------------- -------------------------------------------------------------------------------- ------------------- ------- -------- ------------------------------------------------------------------------------- ---------- ---------- ---------- ------------ ------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
PROGRAM_1                      STORED_PROCEDURE proc_arg                                                                                           0 TRUE    FALSE                                                                                             3          1                                                                                                         NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC program 1 comments
 
SQL> select * from user_scheduler_schedules;
 
SCHEDULE_NAME                  SCHEDULE_TYPE START_DATE                                                                       REPEAT_INTERVAL                                                                  EVENT_QUEUE_OWNER              EVENT_QUEUE_NAME               EVENT_QUEUE_AGENT              EVENT_CONDITION                                                                  FILE_WATCHER_OWNER                                                               FILE_WATCHER_NAME                                                                END_DATE                                                                         COMMENTS
------------------------------ ------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
MY_SCHEDULE                    CALENDAR      31-JAN-13 11.03.49.798000 AM +08:00                                              FREQ=DAILY;BYHOUR=12,13                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           my_schedule_comment
 
SQL> begin
  2  dbms_scheduler.create_job(job_name=>'job_program_schedule',program_name=>'program_1',schedule_name=>'my_schedule');
  3  end;
  4  /
 
PL/SQL procedure successfully completed

複製job
Copying Jobs
  唯一與源job區別是,有新的job name且狀態為disable
 
 
---複製job自job_program_schedule 
SQL> begin
  2  dbms_scheduler.copy_job(old_job => 'job_program_schedule',new_job => 'copy_job');
  3  end;
  4  /
 
PL/SQL procedure successfully completed
 
SQL> select * from user_scheduler_programs;
 
PROGRAM_NAME                   PROGRAM_TYPE     PROGRAM_ACTION                                                                   NUMBER_OF_ARGUMENTS ENABLED DETACHED SCHEDULE_LIMIT                                                                    PRIORITY     WEIGHT   MAX_RUNS MAX_FAILURES MAX_RUN_DURATION                                                                NLS_ENV                                                                          COMMENTS
------------------------------ ---------------- -------------------------------------------------------------------------------- ------------------- ------- -------- ------------------------------------------------------------------------------- ---------- ---------- ---------- ------------ ------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
PROGRAM_1                      STORED_PROCEDURE proc_arg                                                                                           0 TRUE    FALSE                                                                                             3          1                                                                                                         NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC program 1 comments

---檢視複製後的job資訊
SQL> select * from user_scheduler_jobs;
 
JOB_NAME                       JOB_SUBNAME                    JOB_STYLE   JOB_CREATOR                    CLIENT_ID                                                        GLOBAL_UID                       PROGRAM_OWNER                                                                    PROGRAM_NAME                                                                     JOB_TYPE         JOB_ACTION                                                                       NUMBER_OF_ARGUMENTS SCHEDULE_OWNER                                                                   SCHEDULE_NAME                                                                    SCHEDULE_TYPE START_DATE                                                                       REPEAT_INTERVAL                                                                  EVENT_QUEUE_OWNER              EVENT_QUEUE_NAME               EVENT_QUEUE_AGENT                                                                EVENT_CONDITION                                                                  EVENT_RULE                                                        FILE_WATCHER_OWNER                                                               FILE_WATCHER_NAME                                                                END_DATE                                                                         JOB_CLASS                      ENABLED AUTO_DROP RESTARTABLE STATE           JOB_PRIORITY  RUN_COUNT   MAX_RUNS FAILURE_COUNT MAX_FAILURES RETRY_COUNT LAST_START_DATE                                                                  LAST_RUN_DURATION                                                               NEXT_RUN_DATE                                                                    SCHEDULE_LIMIT                                                                  MAX_RUN_DURATION                                                                LOGGING_LEVEL STOP_ON_WINDOW_CLOSE INSTANCE_STICKINESS RAISE_EVENTS                                                                     SYSTEM JOB_WEIGHT NLS_ENV                                                                          SOURCE                                                                           NUMBER_OF_DESTINATIONS DESTINATION_OWNER                                                                DESTINATION                                                                      CREDENTIAL_OWNER               CREDENTIAL_NAME                INSTANCE_ID DEFERRED_DROP ALLOW_RUNS_IN_RESTRICTED_MODE COMMENTS                                                                              FLAGS
------------------------------ ------------------------------ ----------- ------------------------------ ---------------------------------------------------------------- -------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------- -------------------------------------------------------------------------------- ------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------- --------- ----------- --------------- ------------ ---------- ---------- ------------- ------------ ----------- -------------------------------------------------------------------------------- ------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- ------------- -------------------- ------------------- -------------------------------------------------------------------------------- ------ ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ----------- ------------- ----------------------------- -------------------------------------------------------------------------------- ----------
JOB_PROGRAM_SCHEDULE                                          REGULAR     SCOTT                                                                                                                            SCOTT                                                                            PROGRAM_1                                                                                                                                                                                              SCOTT                                                                            MY_SCHEDULE                                                                      NAMED                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                DEFAULT_JOB_CLASS              FALSE   TRUE      FALSE       DISABLED                   3          0                        0                        0                                                                                                                                                                                                                                                                                                                                                                                                                   OFF           FALSE                TRUE                                                                                                 FALSE           1 NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC                                                                                                       1                                                                                                                                                                                                                                             FALSE         FALSE                                                                                                             4195376
COPY_JOB                                                      REGULAR     SCOTT                                                                                                                            SCOTT                                                                            PROGRAM_1                                                                                                                                                                                              SCOTT                                                                            MY_SCHEDULE                                                                      NAMED                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                DEFAULT_JOB_CLASS              FALSE   TRUE      FALSE       DISABLED                   3          0                        0                        0                                                                                                                                                                                                                                                                                                                                                                                                                   OFF           FALSE                TRUE                                                                                                 FALSE           1 NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC                                                                                                       1                                                                                                                                                                                                                                             FALSE         FALSE                                                                                                             4195376