运行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