oracle dba_job不run,处理JOB不能运行的问题。

最新推荐文章于 2023-09-13 09:38:39 发布

fasc chen

最新推荐文章于 2023-09-13 09:38:39 发布

阅读量1k

点赞数

文章标签： oracle dba_job不run

处理JOB不能运行的问题。

1、首先查看job_queue_processes这个参数，如果这个参数为0，说明所有JOb都不能运行。

SQL> show parameter job

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

job_queue_processes integer 10

如果值是 0 需要修改(如果为0则无法运行job，此值为允许同时运行的job数量)

alter system set job_queue_processes =10 scope=spfile;这样要重启数数据库生效。还要不要重启数据库的方法：alter system set job_queue_processes=10;这个只内存中改了，重启以后就不生效了。 --或者其他值

2、再次要学会oracle job 定时,启动，停止，察看状态。运行脚本

job里面的 last_date 和 next_date ，以及interval之间是什么关系，last_date到底是记的 job 的开始执行时间，还是执行结束的时间。next_date到底是 job 的开始时间还是结束时间加上 interval 得出的呢？

如果一个Job的执行时间比较长(比如超过了interval)，会不会出现多个同样的 job 同时执行呢？

last_date=开始时间

last_date+interval=next_date

但要清楚，next_date 通常不是真正的开始时间，真正的开始时间往往比next_date 晚几秒，

也就是延时。

2、不会。

job的next time是在job开始执行的时候就算好了

不过是在job成功执行之后才写上去

当job的执行时间长过interval时候也就是说在job的执行过程中nexttime就已经过了

那么nexttime就变为job执行完时的时间

你所说的情况不存在

SQL> exec dbms_job.broken(21,true);

PL/SQL 过程被成功完成

SQL> select * from user_jobs;

JOB LOG_USER PRIV_USER SCHEMA_USER LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC TOTAL_TIME BROKEN INTERVAL FAILURES WHAT NLS_ENV MISC_ENV INSTANCE

---------- ------------------------------ ------------------------------ ------------------------------ ----------- ---------------- ----------- ---------------- ----------- ---------------- ---------- ------ -------------------------------------------------------------------------------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------------- ----------

21 UNIONS UNIONS UNIONS 2006-11-30 16:32:27 4000-1-1 00:00:00 0 Y trunc(sysdate)+16/24+1 0 ACHIEVETOHISTORY; NLS_LANGUAGE='SIMPLIFIED CHINESE' NLS_TERRITORY='CHINA' NLS_CURRENCY='RMB' NLS_I 0102000200000000 0

SQL> exec dbms_job.run(21);

PL/SQL 过程被成功完成

SQL> select * from user_jobs;

JOB LOG_USER PRIV_USER SCHEMA_USER LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC TOTAL_TIME BROKEN INTERVAL FAILURES WHAT NLS_ENV MISC_ENV INSTANCE

21 UNIONS UNIONS UNIONS 2006-11-30 16:46:14 2006-12-1 1 16:00:00 0 N trunc(sysdate)+16/24+1 0 ACHIEVETOHISTORY; NLS_LANGUAGE='SIMPLIFIED CHINESE' NLS_TERRITORY='CHINA' NLS_CURRENCY='RMB' NLS_I 0102000200000000 0

试写一计划任务：

declare

v_job number :=1;

begin

dbms_job.submit(v_job,'sp_fact_charge_code;',sysdate,'sysdate+1/1440');

commit;

end;

解释一下上面的程序

程序主体有四个参数，分别意为：v_job是计划任务号，'sp_fact_charge_code'是计划任务名，如果是多个计划任务，就都用分号隔开，第三个sysdate意为立即执行此任务，第四个参数是间隔时间的设置，此处为每分钟执行一次，1/1440=1/24/60。

查看任务：select * from user_jobs;select * from all_jobs;

查看正在运行的任务(不推荐使用，速度慢)：select * from dba_jobs_running;

另外值得一提的是，在安装oracle配置的时候，有这么一个参数：

job_queue_processes=4 (默认4)

这个参数是定义当前最多可同时运行几个job，它的最大值能设置为36。

除了submit参数外，其余的几个参数有：

dbms_job.run(v_job); //运行job

dbms_job.broken(v_job,true,next_date); //停止一个job,里面参数true也可是false，next_date(某一时刻停止)也可是sysdate(立刻停止)。

dbms_job.remove(v_job); //删除某个job

dbms_job.what(v_job,'sp_fact_charge_code;'); //修改某个job名

dbms_job.next_date(v_job,sysdate); 修改下一次运行时间

例题，设定每天2:10:10运行

trunc(sysdate)+2/24+10/24/60+10/24/60/60 //运行时间

trunc(sysdate)+1+2/24+10/24/60+10/24/60/60 //间隔运行时间

例题，设定每月2号的2:10:10运行

trunc(sysdate,'mm')+1+2/24+10/24/60+10/24/60/60 //运行时间

trunc(add_mouths(sysdate,1),'mm')+1+2/24+10/24/60+10/24/60/60 //间隔运行时间

例题，设定每个季度……

trunce(sysdate,'Q')+1+2/24+10/24/60+10/24/60/60 //运行时间

trunce(add_mouths(sysdate,3),'Q'))+1+2/24+10/24/60+10/24/60/60 //间隔运行时间

另外年为'Y;

例题，设定每周一……

next_day(sysdate'星期一')

……

找出正在执行的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_RUNNING;

SID JOB

---------- ----------

12 116

16 117

2，查询正在运行的job的信息

SQL> SELECT SID,SERIAL# FROM V$SESSION WHERE SID='12';

SID SERIAL#

---------- ----------

12 4

SQL> SELECT SID,SERIAL# FROM V$SESSION WHERE SID='16';

SID SERIAL#

---------- ----------

16 1

3，利用查询出来的job信息将job结束掉

SQL> ALTER SYSTEM KILL SESSION '12,4';

System altered.

SQL> ALTER SYSTEM KILL SESSION '16,1';

System altered.

4，如果不希望运行上述job的话，可以将job设置为broken.

EXEC DBMS_JOB.BROKEN(116,TRUE);

EXEC DBMS_JOB.BROKEN(117,TRUE);

根据个人经验，这种方法并不会立即中断job的运行。最好是找到job对应的线程kill掉。

3、下面是一个创建JOB简单例子：

创建测试表

SQL> create table TEST(a date);

表已创建。

创建一个自定义过程

SQL> create or replace procedure MYPROC as

2　begin

3　insert into TEST values(sysdate);

4　end;

5　/

过程已创建。

创建JOB

SQL> variable job1 number;

SQL>

SQL> begin

2　dbms_job.submit(:job1,'MYPROC;',sysdate,'sysdate+1/1440');　　--每天1440分钟，即一分钟运行test过程一次

3　end;

4　/

PL/SQL 过程已成功完成。

运行JOB

SQL> begin

2　dbms_job.run(:job1);

3　end;

4　/

PL/SQL 过程已成功完成。

SQL> select to_char(a,'yyyy/mm/dd hh24:mi:ss') 时间 from TEST;

时间

-------------------

2001/01/07 23:51:21

2001/01/07 23:52:22

2001/01/07 23:53:24

删除JOB

SQL> begin

2　dbms_job.remove(:job1);

3　end;

4　/

PL/SQL 过程已成功完成。

3 上面的例子没有涉及到存储过程带参数的问题，如果带参数假设为

MYPROC( ina IN VARCHAR2,

------------------以下为必须输出参数组---------------------------------

outa OUT NUMBER,

outb OUT VARCHAR2)

则在建立job的代码中

创建JOB

begin

sys.dbms_job.submit(job => :job,

what => '

declare

outa number; --定义输出变量

outb varchar2(40);

begin

MYPROC(''输入'',outa =>outa ,outb =>outb );

end;',

next_date => to_date('10-10-2006 08:48:34', 'dd-mm-yyyy hh24:mi:ss'),

interval => 'sysdate+1');

commit;

end;

4、还有在pl/sql developer 8中不能查看JOB

在其他版本中都能查看。

可以用oracle sqldeveloper 2.0来查看它JOB

Job scheduling from Oracle 10g with dbms_scheduler

In Oracle 10g the DBMS_JOB package is replaced by the DBMS_SCHEDULER package. The DBMS_JOB package is now depricated and in Oracle 10g it's only provided for backward compatibility. From Oracle 10g the DBMS_JOB package should not be used any more, because is could not exist in a future version of Oracle.

With DBMS_SCHEDULER Oracle procedures and functions can be executed. Also binary and shell-scripts can be scheduled.

Rights

If you have DBA rights you can do all the scheduling. For administering job scheduling you need the priviliges belonging to the SCHEDULER_ADMIN role. To create and run jobs in your own schedule you need the 'CREATE JOB' privilege.

With DBMS_JOB you needed to set an initialization parameter to start a job coordinator background process. With Oracle 10g DBMS_SCHEDULER this is not needed any more.

If you want to user resource plans and/or consumer groups you need to set a system parameter:

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

Getting started quickly

To quickly get a job running, you can use code like this:

begin

dbms_scheduler.create_job(

job_name => 'DEMO_JOB_SCHEDULE'

,job_type => 'PLSQL_BLOCK'

,job_action => 'begin package.procedure(''param_value''); end; '

,start_date => '01/01/2006 02:00 AM'

,repeat_interval => 'FREQ=DAILY'

,enabled => TRUE

,comments => 'Demo for job schedule.');

end;

This schedules a pl/sql block to be executed daily starting 1/1/2006 02:00 AM.

You can schedule things like this, but DBMS_SCHEDULER can reuse components.

You can build a schedule using components like program, schedule, job, job class and window. We will now discuss these components in detail.

Program

The program component represents program-code that can be executed. This program code can have parameters. Code example

begin

dbms_scheduler.create_program (

program_name => 'DEMO_JOB_SCHEDULE'

,program_type => 'STORED_PROCEDURE'

,program_action => 'package.procedure'

,number_of_arguments => 1

,enabled => FALSE

,comments => 'Demo for job schedule.');

dbms_scheduler.define_program_argument (

program_name => 'DEMO_JOB_SCHEDULE'

,argument_position => 1

,argument_name => 'kol1'

,argument_type => 'VARCHAR2'

,default_value => 'default'

);

dbms_scheduler.enable(name => 'DEMO_JOB_SCHEDULE');

end;

The parameter program_type can have one of the following values: 'PLSQL_BLOCK', 'STORED_PROCEDURE','EXECUTABLE'.

dbms_scheduler also allows to execute shell scripts (Windows: *.bat files) and executables.

Schedule

A schedule defines the frequence and date/time specifics of the start-time for the job.

example code

begin

dbms_scheduler.create_schedule(

schedule_name => 'DEMO_SCHEDULE'

, start_date => '01/01/2006 22:00:00'

, repeat_interval => 'FREQ=WEEKLY'

, comments => 'Weekly at 22:00');

END;

To drop the schedule:

begin

dbms_scheduler.drop_schedule(

schedule_name => 'DEMO_SCHEDULE'

, force => TRUE );

end;

Calendar expresions can have one of these values: 'Yearly','Monthly','Weekly','Daily','Hourly','Minutely','Secondely'

Job

A job defines when a specific task will be started. This can be done by assigning a program to one or more schedules (or to a specific date/time). A job can belong to only 1 job class. Code example

begin

dbms_scheduler.create_job(

job_name => 'DEMO_JOB1'

, program_name =>'DEMO_JOB_SCHEDULE'

, schedule_name =>'DEMO_SCHEDULE'

, enabled => FALSE

, comments => 'Run demo program every week at 22:00');

dbms_scheduler.set_job_argument_value(

job_name => 'DEMO_JOB1'

, argument_position => 1

, argument_value => 'param1');

dbms_scheduler.enable('DEMO_JOB1');

commit;

end;

Or start shell script

begin

dbms_scheduler.create_job

(

job_name => 'RUN_SHELL1',

schedule_name => 'DEMO_SCHEDULE',

job_type => 'EXECUTABLE',

job_action => '/home/test/run_script.sh',

enabled => true,

comments => 'Run shell-script'

);

end;

Monitoring job-scheduling

Jobs can be monitored using Oracle Enterprise Manager 10g. It's also possible to use a number of views that have been created in Oracle 10g. We will discuss some of these views here.

To show details on job run:

select log_date

, job_name

, status

, req_start_date

, actual_start_date

, run_duration

from dba_scheduler_job_run_details

To show running jobs:

select job_name

, session_id

, running_instance