如何在Oracle中管理计划任务

Oracle 定时执行计划任务

Oracle 在10g 版本以前，计划任务用的是DBMS_JOB 包，10g 版本引入DBMS_SCHEDULER来替代先前的DBMS_JOB,在功能方面,它比DBMS_JOB 提供了更强大的功能和更灵活的机制管理，但DBMS_JOB 包的使用相对比较简单，也基本能够满足定时执行计划任务的需求，故接下来就先看看DBMS_JOB 包的使用方法。

1 DBMS_JOB

我们可以在命令窗口输入show parameter job_queue_processes 查看数据库中定时任务的最多并发数，一般设置为10（设置方法：alter system set job_queue_processes=10），如果设为0，那么数据库定时作业是不会运行的。

oracle 定时执行job queue 的后台进程是SNP,要启动snp,首先看系统模式是否支持sql> alter system enable restricted session;或sql> alter system disenable restricted session;利用上面的命令更改系统的会话方式为disenable restricted,为snp 的启动创建条件

接下来我们尝试实现以下功能：每隔一分钟自动向job_test 表中插入当前的系统时间。

1、创测试表

create table job_test(updatetime date);

2、创建JOB

variable v_job_no number;

begin

dbms_jobsubmit(:v_job_no, 'insert into job_test values(sysdate);', sysdate,'sysdate+1/1440');

end;

/

其中最后一个参数'sysdate+1/1440'表示时间间隔为每分钟。其它常用的时间间隔的设置如下：

（1）如果想每天凌晨1 点执行，则此参数可设置为'trunc(sysdate)+25/24'；（2）如果想每周一凌晨1 点执行，则此参数可设置为'trunc(next_day(sysdate,1))+25/24'；

（3）如果想每月1 号凌晨1 点执行，则此参数可设置为'trunc(last_day(sysdate))+25/24'；

（4）如果想每季度执行一次，则此参数可设置为'trunc(add_months(sysdate,3),'Q')+1/24'；（5）如果想每半年执行一次，则此参数可设置为'add_months(trunc(sysdate,'yyyy'),6)+1/24'；（6）如果想每年执行一次，则此参数可设置为'add_months(trunc(sysdate,'yyyy'),12)+1/24'。

select from user_jobs;--查看当前用户的调度任务select from dba_jobs_running;--查看正在执行的调度任务select from dba_jobs;--查看执行完的调度任务select from all_jobs; -- 查看数据库系统所有任务实例1：

1 创建测试表

create table a(a date);

2 创建一个存储过程

create or replace procedure test as

begin

insert into a values(sysdate);

end;

/

3 创建JOB（任务计划）

variable job1 number;

begin

dbms_jobsubmit(:job1,'test;',sysdate,'sysdate+1/1440');end;

/

--每天1440分钟，即一分钟运行test存储过程一次4 运行JOB

begin

dbms_jobrun(:job1);

end;

/

5 查看执行结果

select to_char(a,'yyyy/mm/dd hh24:mi:ss') 时间 from a;6删除JOB

begin

dbms_jobremove(:job1);

end;

/

job的使用说明：

DBMS_JOBSUBMIT(:jobno,//job号

'your_procedure;',//要执行的过程

trunc(sysdate)+1/24,//下次执行时间

'trunc(sysdate)+1/24+1'//每次间隔时间

);

删除job:dbms_jobremove(jobno);

修改要执行的 *** 作:job:dbms_jobwhat(jobno,what);修改下次执行时间：dbms_jobnext_date(job,next_date);修改间隔时间：dbms_jobinterval(job,interval);停止job:dbmsbroken(job,broken,nextdate);启动job:dbms_jobrun(jobno);

例子：

VARIABLE jobno number;

begin

DBMS_JOBSUBMIT(:jobno,

'Procdemo;',//Procdemo为过程名称

SYSDATE, 'SYSDATE + 1/720');

commit;

end;

/

描述 Interval参数值

每天运行一次

'SYSDATE + 1'

每小时运行一次

'SYSDATE + 1/24'

每10分钟运行一次

'SYSDATE + 10/（6024）'

每30秒运行一次

'SYSDATE + 30/(602460)'

每隔一星期运行一次

'SYSDATE + 7'

不再运行该任务并删除它

NULL

每天午夜12点

'TRUNC(SYSDATE + 1)'

每天早上8点30分

'TRUNC(SYSDATE + 1) + （860+30）/(2460)'

每星期二中午12点

'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24'

每个月第一天的午夜12点

'TRUNC(LAST_DAY(SYSDATE ) + 1)'

每个季度最后一天的晚上11点

'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24'

每星期六和日早上6点10分

'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + （6×60+10）/（24×60）'

2 DBMS_SCHEDULER

DBMS_SCHEDULER的功能更强大，定义更灵活，增强了与系统的交互性。可以有两种方式来定义"计划"：

1) 使用DBMS_SCHDULERCREATE_SCHEDULE //定义一个计划，计划再调用job;2) 调用DBMS_SCHDULERCREATE_JOB //过程直接定义job例子1：用DBMS_SCHDULERCREATE_JOBE直接创建jobBEGIN

DBMS_SCHEDULERCREATE_JOB (

job_name => 'update_bb', //job的名字

job_type => 'STORED_PROCEDURE', //job的类型为“执行存储过程”

job_action => 'OPSSALES_PKGUPDATE_SALES_SUMMARY', //存储过程的名字start_date => '28-APR-10 070000 PM Australia/Sydney', //这里把时区去掉也可以！

repeat_interval => 'FREQ=DAILY;INTERVAL=2', / every other day /enabled =>true, //启动该job

comments => 'My new job'); //注释

END;

/

参数说明：

·job_name: 顾名思义,每个job都必须有一个的名称·schedule_name: 如果定义了计划，在这里指定计划的名称·job_type: 目前支持三种类型:

·PLSQL_BLOCK : PL/SQL块: 需要输入完整的PL/SQL代码;·STORED_PROCEDURE : 存储过程: 需要指定存储过程的名字;·EXECUTABLE: 外部程序: (外部程序可以是一个shell脚本,也可以是 *** 作系统级别的指令) 需要输入script的名称或者 *** 作系统的指令名·enabled: 上面已经说过了，指定job创建完毕是否自动激活·comments: 对于job的简单说明

例子2：

BEGIN

DBMS_SCHEDULERCREATE_JOB (

job_name => 'zbb_job3',

job_type => 'PLSQL_BLOCK', //job的类型是执行sql语句job_action => 'insert into bb values(1,sysdate);',start_date => sysdate,

repeat_interval => 'freq = minutely; interval=1', //每分钟执行一次enabled => true,

comments => 'my love');

END;

/

注意： enabled 默认为false，oracle不会运行此job，所有我们需要enable它SQL> exec dbms_schedulerenable ('zbb_job3');例子3：使用DBMS_SCHDULERCREATE_SCHEDULE

建立 scheduler（控制时间和频率），然后给它添加几个jobs（动作）！

BEGIN

DBMS_SCHEDULERCREATE_SCHEDULE ( //创建计划任务schedule_name => 'zbb_schedule',

repeat_interval => 'FREQ=MINUTELY; INTERVAL=5', //执行间隔：每5分钟comments => 'Every 5 MINUTS');

END;

/

接下来往“zbb_schedule”里添加一个job “zbb_job2”：

BEGIN

DBMS_SCHEDULERCREATE_JOB ( //创建job 并把它加入到scheduler里面job_name => 'zbb_job2',

job_type => 'PLSQL_BLOCK',

job_action => 'insert into bb values(1,sysdate);',schedule_name => 'ZBB_SCHEDULE');

END;

/

再往“zbb_schedule”里添加一个job “zbb_job3”：

BEGIN

DBMS_SCHEDULERCREATE_JOB (

job_name => 'zbb_job3',

job_type => 'PLSQL_BLOCK',

job_action => 'insert into bb values(1,sysdate);',schedule_name => 'ZBB_SCHEDULE');

END;

/

运行“select job_name,schedule_name from user_scheduler_jobs;”可以查看到所有的job。

也可以将“zbb_job1” 加入到 “zbb_schedule”

SQL> begin

2 dbms_schedulerSET_ATTRIBUTE( //注意这里执行的是修改属性的过程3 name => 'zbb_job1',

4 attribute => 'schedule_name',

5 value => 'ZBB_SCHEDULE');

6 end;

7 /

删除 job 及 scheduler：

SQL> BEGIN

DBMS_SCHEDULERDROP_JOB ( //删除job；多个job间用逗号隔开job_name => 'zbb_job2,zbb_job3',

force => TRUE);

END;

/

如何删除scheduler：

BEGIN

DBMS_SCHEDULERDROP_SCHEDULE (

schedule_name => 'zbb_schedule',

force => true ); // 设为true 强制；false。

END;

/

如何修改job的属性（frequency：频率）：

BEGIN

DBMS_SCHEDULERSET_ATTRIBUTE (

name => 'zbb_job1',

attribute => 'repeat_interval',

value => 'FREQ=DAILY'); //每天执行一次

END;

/

jobs创建时为“disabled”状态。我们必须手动“enable”它:

BEGIN

DBMS_SCHEDULERENABLE ('job1, job2, job3, sysjobclass1, sysjobclass2, sysjobclass3'); / sysjobclass1下的所有jobs都会被enable /END;/END;

/

查看job的状态：

SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'MY_EMP_JOB1';查看Job Logs：

SELECT JOB_NAME, OPERATION, OWNER FROM DBA_SCHEDULER_JOB_LOG;查看Job 运行的详细情况：

select log_id, job_name, status, to_char(log_date, 'DD-MON-YYYY HH24:MI') log_date from dba_scheduler_job_run_detailswhere job_name = 'MY_JOB14';如何管理计划任务的权限：

GRANT SCHEDULER_ADMIN TO username;GRANT CREATE JOB TO scott;GRANT ALTER myjob1 TO scott;GRANT MANAGE SCHEDULER TO adam;PLSQL举例如下：

begin dbms_schedulercreate_job ( job_name => '测试', job_type => 'PLSQL_BLOCK', job_action => 'insert into job_test values(sysdate);', start_date => sysdate, end_date => add_months(sysdate,12000), repeat_interval => 'FREQ=MINUTELY; INTERVAL=1', enabled => true,comments => '定时执行计划任务' );

end;

以上代码可以通过在PLSQL Developer中新建Jobs轻松设定：

参数含义如下：

JOB_NAME ：指定任务的名称，必选值，注意要确保指定的名称唯一。

JOB_TYPE ：任务执行的 *** 作类型，必选值，有下列几个可选值：

PLSQL_BLOCK ：表示任务执行的是一个PL/SQL匿名块。

STORED_PROCEDURE ：表示任务执行的是ORACLE过程(含PL/SQL PROCEDURE和JAVA PROCEDURE)。

EXECUTABLE ：表示任务执行的是一个外部程序，比如说 *** 作系统命令。

CHAIN ：表示任务执行的是一个CHAIN。

JOB_ACTION ：任务执行的 *** 作，必选值，应与JOB_TYPE类型中指定的参数相匹配。比如说对于PL/SQL匿名块，此处就可以放置PL/SQL块的具体代表，类似DECLARE BEGIN END这类；如果是ORACLE过程，那么此处应该指定具体的过程名，注意由于任务执行，即使过程中有OUT之类参数，实际执行时也不会有输出的。

START_DATE ：指定任务初次执行的时间，本参数可为空，当为空时，表示任务立刻执行，效果等同于指定该参数值为SYSDATE。

REPEAT_INTERVAL ：指定任务执行的频率，比如多长时间会被触发再次执行。本参数也可以为空，如果为空的话，就表示当前设定的任务只执行一次。 REPEAT_INTERVAL参数需要好好说说，REPEAT_INTERVAL

分组查询(group by)

分组查询:

1查询每个部门的最高工资

select deptno,max(sal) from emp group by deptno;

2查询每个职位的平均工资

select deptno,avg(sal) from emp group by deptno;

3查询每个部门的人数

select deptno,count() from emp group by deptno;

4查询工资大于1000的员工,每个部门的最大工资

select deptno,max(sal) from emp where sal>1000 group by deptno;

多字段分组查询:group by 字段1,字段2;

1查询每个部门下每个主管的手下人数

select deptno, mgr, count() from emp where mgr is not null group by deptno,mgr;

2查询emp表中每个部门的编号,人数,工资总和,最后根据人数进行升序排序,如果人数一致,根据工资总和降序排序

select deptno,count(),sum(sal) from emp group by deptno order by count() asc,sum(sal) desc;

3查询工资在1000-3000之间的员工信息,每个部门的编号,平均工资,最低工资,最高工资,根据平均工资进行升序排序排列

select deptno,avg(sal),min(sal),max(sal) from emp where sal between 1000 and 3000 group by deptno order by avg(sal)

4查询含有上级领导的员工,每个职业的人数,工资的总和,平均工资,最低工资,最后根据人数进行降序排列,如果人数一致,根据平均工资进行升序排列

select job, count(),avg(sal),min(sal) from emp where mgr is not null group by job order by count() desc,avg(sal) asc;

各种关键字的顺序

select from 表名  where grouphaving order by limit

    having(结合group by使用)

having一般要结合分组查询和聚合函数使用,用于给聚合函数的内容添加条件

聚合函数的条件不能写在where后面

普通字段的条件写在where后面,聚合函数的条件写在having后面

1查询每个部门的平均工资,要求平均工资大于2000(c是别名的用法)

select deptno,avg(sal) c from emp group by deptno having c >2000;

2查询每个分类的平均单价,要求平均单价低于100

select category_id ,avg(price) a from t_item group by category_id having a<100;

3查询category_id分类为238和917的两个分类的平均单价

select category_id,avg(price) from t_item where category_id in(238,917) group by category_id;

4查询emp表中每个部门的平均工资高于2000的部门编号,部门人数,平均工资,最后根据平均工资降序排列

select deptno,count(),avg(sal) a from emp group by deptno having a>2000 order by a desc;

5查询emp表中工资在1000-3000之间的员工,每个部门编号,工资总和,平均工资,过滤掉平均工资低于2000的部门,按照平均工资进行升序排序

select deptno,sum(sal), avg(sal) a from emp where sal between 1000 and 3000 group by deptno having a>=2000 order by a asc;

6查询emp表中每年入职的人数

select extract(year from hiredate) year,count() from emp group by year;

7查询每个部门的最高平均工资

select deptno,avg(sal) from emp group by deptno order by avg(sal) limit 0,1;

子查询(嵌套查询)

子查询可以写在where或having后面当做查询条件的值

写在from后面,当做一张新表(但是必须要有别名)

select ename from (select from emp where sal>1000) newtable;

写在创建表的时候

create table emp_20 as (select from emp where deptno=20);

1查询emp表中工资最高的员工信息

select from emp where sal=(select max(sal) from emp);

2查询emp表中工资大于平均工资的所有员工的信息

select from emp where sal>(select avg(sal) from emp);

3查询工资高于20号部门最大工资的员工信息

select from emp where sal>(select max(sal) from emp where deptno=20);

4查询工资高于20号部门最大工资的员工信息

select from emp where sal>(select avg(sal) from emp);

5查询和Jones相同工资的其他员工信息

select from emp where job=(select job from emp where ename='jones' and ename!='jones');

6查询工资最低的员工的同事们的信息(同事=相同job)

select from  emp where job=(select job from emp where sal=(select min(sal) from emp)) and sal !=(select min(sal) from emp);

7查询最晚入职的员工信息

select from emp where hiredate=(select max(hiredate) from emp);

8查询名字为King的部门编号和部门名称(需要用到dept表)

select deptno,dname from dept where deptno=(select deptno from emp where ename='king');

9查询有员工的部门信息(编号和名称)

select deptno ,dname from dept where deptno in (select distinct deptno from emp);

10查询平均工资最高的部门信息

select from dept where deptno in (select deptno from emp group by deptno having avg(sal)=(select avg(sal) from emp group by deptno order by avg(sal) desc limit 0,1));

关联查询

同时查询多张表的数据称为关联查询

1查询每一个员工的名称和其对应的部门名称

select eename,ddname from emp e,dept d where edeptno=ddeptno;

2查询在new york工作的所有员工的信息

select  e from emp  e,dept  d  where edeptno=ddeptno and  dloc='new york';

笛卡尔积

如果关联查询不写关联关系则查询到的数据是两张表的乘积,这个乘积称为笛卡尔积,笛卡尔是一种错误查询方式的结果,工作切记不要出现

    等值连接和内连接

等值连接:

select from A,B where Ax=Bx and Aage=18;

内连接:

select from A join B on Ax=Bx where Aage=18;(将关联关系写在on后面)

1查询每个员工的名称和其对应的部门名称

select eename,ddname from emp e join dept d on edeptno=ddeptno;

外连接

使用外连接查询得到的数据层除了两张表的交集数据以外和另外一张主表的全部数据,哪个表为主表通过left/rigth控制, left以join左边表为主表 rigth以join右边表为主表

1查询所有员工的名称和其对应的部门名称

select eename,ddname from emp e left join dept d on edeptno=ddeptno;

先查看你job的jobid

select from dba_jobs ;

然后根据jobid来启动job

exec dbms_jobrun(job => &JOBID) ;

数据库中建表。建表模版在Quartz包下docs/dbTables下，选择相应的数据库和版本即可。ORACLE的11个Table列表如下：

QRTZ_JOB_LISTENERS：存储有关已配置的 JobListener 的信息

JOB_NAME

JOB_GROUP

JOB_LISTENER

QRTZ_TRIGGER_LISTENERS：存储已配置的 TriggerListener 的信息

QRTZ_FIRED_TRIGGERS：存储与已触发的 Trigger 相关的状态信息，以及相联 Job的执行信息

QRTZ_PAUSED_TRIGGER_GRPS：存储已暂停的 Trigger 组的信息

QRTZ_SCHEDULER_STATE：存储集群中note实例信息，quartz会定时读取该表的信息判断集群中每个实例的当前状态

INSTANCE_NAME 之前配置文件中orgquartzschedulerinstanceId配置的名字，就会写入该字段，如果设置为AUTO,quartz会根据物理机名和当前时间产生一个名字

LAST_CHECKIN_TIME：上次检查时间

CHECKIN_INTERVAL ：检查间隔时间

QRTZ_LOCKS：存储程序的悲观锁的信息(假如使用了悲观锁)

QRTZ_SIMPLE_TRIGGERS：存储简单的Trigger，包括重复次数，间隔，以及已触的次数

TRIGGER_NAME ：qrtz_triggers表trigger_name的外键

TRIGGER_GROUP：qrtz_triggers表trigger_group的外键

REPEAT_COUNT ：重复次数

REPEAT_INTERVAL：时间间隔

TIMES_TRIGGERED：触发次数

QRTZ_CRON_TRIGGERS：存储cron表达式表

TRIGGER_NAME ：qrtz_triggers表trigger_name的外键

TRIGGER_GROUP：qrtz_triggers表trigger_group的外键

CRON_EXPRESSION：cron表达式

TIME_ZONE_ID ：时区

QRTZ_TRIGGERS：保存trigger信息

对于Oracle的RAC集群数据库，一直以来对于高度JOB方面如何做到负载均衡，一直都不太清楚，这几天测试了一下，得出以下结论。

一个JOB在何级别运行是可以定制的。如果把job定义在db级，job可以运行在任何活动的instance上，并遵循job的调度机制；

如果把job定义在instance级别上，job将运行在指定的实例上，如因某种异常导致创建job的实例当机，那job将运行在存活的实例上。

1、目前我们的rac数据库是通过查询语句 select job,instance,what fromdba_jobs 可以看到instance=0，这表示该job是db级，可以运行在任何活动的instance上，由job的调度机制决定在哪个实例上运行。也就是说RAC会根据两台服务器的运行状态来调度JOB在不同的节点实例中运行，一个JOB可以在A机，下一次有可能在B机运行；

2、通过在调度中指定instance 参数，可以指定job只在某个特定实例上运行，但是如果该实例的服务器出现故障时，发现job 在实例A上不再运行，也不会切换到其它实例。如果job建立时没有指定运行在某个实例上，在job当前运行的实例关掉后，却可以切到其他活动的实例上。

3、一般情况下，建立不要指定JOB在特定实例运行，如果对于对于已经在运行的job，如果想指定其只在某个实例运行。建议先删除此job，然后重建job，重建时指定job运行的实例。对于有人说可以使用如下方式修改job运行的实例：SQL> exec dbms_jobinstance(26,1)。经测试，不好使，此sql执行后，job不再运行，并出现等待事件：enq: TX - row lock contention，查到执行的sql是update sysjob$ setthis_date=:1 where job=:2，也就是在更新sys的sysjob$表，最后只能杀掉此会话，才消除此等待事件。

4、目前还没找到SQL语句来查看某一个JOB运行在哪个实例，一查询，都是0（默认），则可能在任何节点执行。笨方法是通过在Linux中用TOP语句查看CPU的性能来判断是否负荷均衡。

转载，仅供参考。

一般是你的存储过程、函数等有问题，如没有考虑到异常并处理异常，导致程序运行中断造成的。（重启数据库有时也会有这样的问题，所以重启后，要检查一遍是否有未编译通过的地方、检查一下job状态，也可以写一个job来定时检查其他job工作状态）

以上就是关于如何在Oracle中管理计划任务全部的内容，包括:如何在Oracle中管理计划任务、数据库高级查询2、oracle数据库怎么启动job等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！