Chinaunix首页 | 论坛 | 博客
  • 博客访问: 664776
  • 博文数量: 168
  • 博客积分: 2928
  • 博客等级: 中校
  • 技术积分: 1904
  • 用 户 组: 普通用户
  • 注册时间: 2010-01-04 09:56
文章分类

全部博文(168)

文章存档

2010年(168)

我的朋友

分类: Oracle

2010-03-30 17:37:53

处理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、不会。
1
job的next time是在job开始执行的时候就算好了
不过是在job成功执行之后才写上去
当job的执行时间长过interval时候 也就是说在job的执行过程中nexttime就已经过了
那么nexttime就变为job执行完时的时间
2
你所说的情况不存在

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
,      elapsed_time
,      cpu_used
from dba_scheduler_running_jobs;
 
To show job history:
select log_date
,      job_name
,      status
from dba_scheduler_job_log;

show all schedules:
select schedule_name, schedule_type, start_date, repeat_interval
from dba_scheduler_schedules;
show all jobs and their attributes:
select *
from dba_scheduler_jobs

show all program-objects and their attributes
select *
from dba_scheduler_programs;
show all program-arguments:
select *
from   dba_scheduler_program_args;
 
阅读(10568) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~