dbms_scheduler package系列(七)-2

執行job  
Running Jobs  
 兩種執行方式：非同步和同步
 
 
非同步：根據schedule的配置，job會提交給job coordinator然後被job slave選中進行執行
 You can schedule a job to run asynchronously based on the schedule defined when the job is created. In this case,
 the job is submitted to the job coordinator and is picked up by the job slaves for execution.
 
---透過如下字典可查詢job執行相關資訊
SQL> select * from user_scheduler_job_run_details where rownum=1;
 
    LOG_ID LOG_DATE                                                                         OWNER                          JOB_NAME                                                                         JOB_SUBNAME                                                                      STATUS                             ERROR# REQ_START_DATE                                                                   ACTUAL_START_DATE                                                                RUN_DURATION                                                                    INSTANCE_ID SESSION_ID                     SLAVE_PID                      CPU_USED                                                                        CREDENTIAL_OWNER                                                                 CREDENTIAL_NAME                                                                  DESTINATION_OWNER                                                                DESTINATION                                                                      ADDITIONAL_INFO
---------- -------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------------------------------------------------------- ----------- ------------------------------ ------------------------------ ------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
      1141 03-FEB-13 10.25.02.689000 PM +08:00                                              SCOTT                          ZXY_JOB                                                                                                                                                           FAILED                              27370 03-FEB-13 12.03.49.400000 PM +08:00                                              03-FEB-13 10.25.01.052000 PM +08:00                                              +000 00:00:01                                                                             1 93,5                           3544                           +000 00:00:00.00                                                                                                                                                                                                                                                                                                                                                                                                    ORA-27370: job slave failed to launch a job of type EXECUTABLE
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       ORA-27300: OS system dependent operation:accessing job scheduler service failed
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       ORA-27301: OS failure message: 管道的另一端上無任何程式。
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       ORA-27302: failure occurred at: sjsec 9
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       ORA-27303: additional information: 管道的另一端上無任何程式。
 
SQL> select * from user_scheduler_job_log where rownum=1;
 
    LOG_ID LOG_DATE                                                                         OWNER                          JOB_NAME                                                                         JOB_SUBNAME                                                                      JOB_CLASS                      OPERATION                      STATUS                         USER_NAME                      CLIENT_ID                                                        GLOBAL_UID                       CREDENTIAL_OWNER                                                                 CREDENTIAL_NAME                                                                  DESTINATION_OWNER                                                                DESTINATION                                                                      ADDITIONAL_INFO
---------- -------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------- -------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
      1141 03-FEB-13 10.25.02.380000 PM +08:00                                              SCOTT                          ZXY_JOB                                                                                                                                                           DEFAULT_JOB_CLASS              RUN                            FAILED                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
 
同步執行job
Running Jobs Synchronously 
  1,必須執行run_job過程且設定use_current_session=true
  2,可以用run_job在指定的schedule之外測試job,但每次測試不會影響failure_count和run_count,
    但會記錄到run log中，如執行出錯會拋錯誤job invoker

---建立同步執行job
SQL> begin
  2  dbms_scheduler.run_job(job_name => 'job_program_schedule',use_current_session => true);
  3  end;
  4  /
 
PL/SQL procedure successfully completed

   
---檢視同步執行job的相關資訊   
SQL> select * from user_scheduler_job_run_details xs where xs.JOB_NAME='JOB_PROGRAM_SCHEDULE';
 
    LOG_ID LOG_DATE                                                                         OWNER                          JOB_NAME                                                                         JOB_SUBNAME                                                                      STATUS                             ERROR# REQ_START_DATE                                                                   ACTUAL_START_DATE                                                                RUN_DURATION                                                                    INSTANCE_ID SESSION_ID                     SLAVE_PID                      CPU_USED                                                                        CREDENTIAL_OWNER                                                                 CREDENTIAL_NAME                                                                  DESTINATION_OWNER                                                                DESTINATION                                                                      ADDITIONAL_INFO
---------- -------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------------------------------------------------------- ----------- ------------------------------ ------------------------------ ------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
      1213 06-FEB-13 02.03.24.705000 PM +08:00                                              SCOTT                          JOB_PROGRAM_SCHEDULE                                                                                                                                              SUCCEEDED                               0 06-FEB-13 02.03.24.609000 PM +08:00                                              06-FEB-13 02.03.24.609000 PM +08:00                                              +000 00:00:00                                                                             1 94,7                                                          +000 00:00:00.00                                                                                                                                                                                                                                                                                                                                                                                                   
 
SQL> select * from user_scheduler_job_log tl where tl.JOB_NAME='JOB_PROGRAM_SCHEDULE';
 
    LOG_ID LOG_DATE                                                                         OWNER                          JOB_NAME                                                                         JOB_SUBNAME                                                                      JOB_CLASS                      OPERATION                      STATUS                         USER_NAME                      CLIENT_ID                                                        GLOBAL_UID                       CREDENTIAL_OWNER                                                                 CREDENTIAL_NAME                                                                  DESTINATION_OWNER                                                                DESTINATION                                                                      ADDITIONAL_INFO
---------- -------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------- -------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
      1213 06-FEB-13 02.03.24.672000 PM +08:00                                              SCOTT                          JOB_PROGRAM_SCHEDULE                                                                                                                                              DEFAULT_JOB_CLASS              RUN                            SUCCEEDED                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           REASON="manually run"
    
 
---測試在指定schedule之外測試run_job是否影響
SQL> begin
  2  dbms_scheduler.create_job(job_name=>'job_schedule',schedule_name=>'my_schedule');
  3  end;
  4  /

--報錯說明要指定job_type及job_action,即具體執行的指令碼相關資訊
begin
dbms_scheduler.create_job(job_name=>'job_schedule',schedule_name=>'my_schedule');
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
 
SQL>
SQL>
SQL> ed
SQL>
---建立指定schedule的job
SQL> begin
  2  dbms_scheduler.create_job(job_name=>'job_schedule',job_type=>'stored_procedure',job_action=>'proc_arg',schedule_name=>'my_schedule');
  3  end;
  4  /
 
PL/SQL procedure successfully completed