oracle中如何创建一个job

将下面的存储过程名称换成实际的存储过程名称，就可以实现您要的功能的，

现为你写的，直接在pl/sql dev里面执行就行了，有啥问题再联系我。

上面一段是删除job，后面的是建立job，我写了判断语句，可以随意执行

参考oracle的 dbms_job包。

---------------------华丽丽的分割线----------------------------

DECLARE

i_count number

job_num number

BEGIN

select count(job) into i_count from user_jobs where upper(what)='存储过程名称'

if i_count>0 then

select job into job_num from user_jobs where upper(what)='存储过程名称'

dbms_job.remove(job_num)

end if

END

/

commit

DECLARE X NUMBER

BEGIN

DBMS_JOB.SUBMIT

( job      => X

,what      => '存储过程名称'

,next_date => to_date(to_char(sysdate+1,'yyyy-mm-dd')||' 01:00:00','yyyy-mm-dd hh24:mi:ss')

,interval  => 'SYSDATE+1'

,no_parse  => TRUE

)

END

/

commit

找出正在执行的JOB编号及其会话编号 SELECT SID,JOB FROM DBA_JOBS_RUNNING停止该JOB的执行 SELECT SID,SERIAL# FROM V$SESSION WHERE SID='&SID'ALTER SYSTEM KILL SESSION '&SID,&SERIAL'EXEC DBMS_JOB.BROKEN(&JOB,TRUE)实例分析：1，查询正在运行的Job，通过查询有两个，和进程占用较多的是两个ORACLE进程符合。SQL>SELECT SID,JOB FROM DBA_JOBS_RUNNINGSID JOB\x0d\x0a---------- ----------12 11616 1172，查询正在运行的job的信息SQL>SELECT SID,SERIAL# FROM V$SESSION WHERE SID='12'SID SERIAL#\x0d\x0a---------- ----------\x0d\x0a12 4SQL>SELECT SID,SERIAL# FROM V$SESSION WHERE SID='16'SID SERIAL#\x0d\x0a---------- ----------\x0d\x0a16 13，利用查询出来的job信息将job结束掉SQL>ALTER SYSTEM KILL SESSION '12,4'System altered.SQL>ALTER SYSTEM KILL SESSION '16,1'System altered.\x0d\x0a4，如果不希望运行上述job的话，可以将job设置为broken.EXEC DBMS_JOB.BROKEN(116,TRUE)EXEC DBMS_JOB.BROKEN(117,TRUE)

ORACLE有一种定时调度机制 用dbms_job包来管理

设置的JOB就是不运行 搞得的郁闷

最好执行了这个才搞定 exec dbms_ijob set_enabled(true)

下面提供一个checklist用于检查job异常的原因

) Instance in RESTRICTED SESSIONS mode?

Check if the instance is in restricted sessions mode:

select instance_name logins from v$instance

If logins=RESTRICTED then:

alter system disable restricted session

^– Checked!

) JOB_QUEUE_PROCESSES=

Make sure that job_queue_processes is >

show parameter job_queue_processes

^– Checked!

) _SYSTEM_TRIG_ENABLED=FALSE

Check if _system_enabled_trigger=false

col parameter format a

col value format a

select a ksppinm parameter b ksppstvl value from x$ksppi a x$ksppcv b

where a indx=b indx and ksppinm= _system_trig_enabled

If _system_trig_enabled=false then

alter system set _system_trig_enabled =TRUE scope=both

^– Checked!

) Is the job BROKEN?

select job broken from dba_jobs where job=<job_number>

If broken then check the alert log and trace files to diagnose the issue

^– Checked! The job is not broken

) Is the job MITted?

Make sure a mit is issued after submitting the job:

DECLARE X NUMBER

BEGIN

SYS DBMS_JOB SUBMIT

(

job =>X

what =>dbms_utility *** yze_schema

( SCOTT PUTE NULL NULL NULL)

next_date =>to_date( / / : : ′ dd/mm/yyyy hh :mi:ss )

no_parse =>FALSE

)

MIT

END

/

If the job executes fine if forced (i e exec dbms_jobs run(<job_no>)) then likely a mit

is missing

^– Checked! The job is mitted after submission

) UPTIME >days

Check if the server (machine) has been up for more than days:

For SUN use uptime OS mand

If uptime>and the jobs do not execute automatically then you are hitting unpublished bug

(Jobs may stop running after days uptime) which is fixed in and A

^– Checked! The server in this case has been up days only

) DBA_JOBS_RUNNING

Check dba_jobs_running to see if the job is still running:

select * from dba_jobs_running

^– Checked! The job is not running

LAST_DATE and NEXT_DATE

Check if the last_date and next_date for the job are proper:

select Job Next_date Last_date from dba_jobs where job=<job_number>

^– NEXT_DATE is porper however LAST_DATE is null since the job never executes automatically

) NEXT_DATE and INTERVAL

Check if the Next_date is changing properly as per the interval set in dba_jobs:

select Job Interval Next_date Last_date from dba_jobs where job=<job_number>

^– This is not possible since the job never gets executed automatically

) Toggle value for JOB_QUEUE_PROCESSES

Stop and restart CJQ process(es)

alter system set job_queue_processes=

–<Wait for some time to ensure CJQ process stopped>

alter system set job_queue_processes=

Ref: Bug (fixed by: )

^– Done but did not help

) DBMS_IJOB(Non documented):

Last ditch effort

Either restart the database or try the following:

exec dbms_ijob set_enabled(true)

Ref: Bug (Closed Not a Bug)

Done but did not help

These are the most mon causes for this behavior

Solution

The solution ended up to be the server (machine) uptime

Even though it was up for only days after the server was rebooted all jobs were able to execute automatically

To implement the solution please execute the following steps:

Shutdown all applications including databases

Shutdown the server (machine)

Restart all applications including databases

Check that jobs are executing automatically

lishixinzhi/Article/program/Oracle/201311/16944

---