oracle chain
[oracle@tyger dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 6 13:02:41 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
/* 创建測试用户tyger 赋予权限 */
SYS@ORCL>drop user tyger cascade;
User dropped.
SYS@ORCL>create user tyger identified by tyger account unlock;
User created.
SYS@ORCL>grant connect,resource to tyger;
Grant succeeded.
SYS@ORCL>conn tyger/tyger
Connected.
/* 创建序列 seq_chain 以及測试表 tab1 tab2 tab3 */
TYGER@ORCL>create sequence seq_chain;
Sequence created.
TYGER@ORCL>create table tab1(id number(5),cdate date,sdate date default sysdate);
Table created.
TYGER@ORCL>create table tab2(id number(5),cdate date,sdate date default sysdate);
Table created.
TYGER@ORCL>create table tab3(id number(5),cdate date,sdate date default sysdate);
Table created.
/* 创建program 报错权限不足 */
TYGER@ORCL>begin
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'CHAIN_P1',
program_type => 'PLSQL_BLOCK',
program_action => 'begin
execute immediate ''insert into tab1(id,cdate)
values(seq_chain.nextval,add_months(sysdate,seq_chain.currval))'';
end;',
enabled => true);
end;
/
2 3 4 5 6 7 8 9 10 11
*
ERROR at line 1:
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 5
ORA-06512: at "SYS.DBMS_SCHEDULER", line 30
ORA-06512: at line 2
/* 赋予必要权限 以及接下去实验须要的权限 */
TYGER@ORCL>conn / as sysdba
Connected.
SYS@ORCL>grant create job to tyger;
Grant succeeded.
SYS@ORCL>grant create evaluation context to tyger;
Grant succeeded.
SYS@ORCL>grant create rule set to tyger;
Grant succeeded.
SYS@ORCL>grant create rule to tyger;
Grant succeeded.
/* 创建program */
SYS@ORCL>conn tyger/tyger
Connected.
TYGER@ORCL>begin
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'CHAIN_P1',
program_type => 'PLSQL_BLOCK',
program_action => 'begin
execute immediate ''insert into tab1(id,cdate)
values(seq_chain.nextval,add_months(sysdate,seq_chain.currval))'';
end;',
enabled => true);
end;
/
2 3 4 5 6 7 8 9 10 11
PL/SQL procedure successfully completed.
TYGER@ORCL>begin
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'chain_step2',
program_type => 'PLSQL_BLOCK',
program_action => 'begin
execute immediate ''insert into tab2(id,cdate)
values(seq_chain.nextval,add_months(sysdate,seq_chain.currval))'';
end;',
enabled => true);
end;
/
2 3 4 5 6 7 8 9 10 11
PL/SQL procedure successfully completed.
TYGER@ORCL>begin
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'CHAIN_P3',
program_type => 'PLSQL_BLOCK',
program_action => 'begin
execute immediate ''insert into tab3(id,cdate)
values(seq_chain.nextval,add_months(sysdate,seq_chain.currval))'';
end;',
enabled => true);
end;
/
2 3 4 5 6 7 8 9 10 11
PL/SQL procedure successfully completed.
/* 创建链tyger_chain */
TYGER@ORCL>exec dbms_scheduler.create_chain('tyger_chain');
PL/SQL procedure successfully completed.
TYGER@ORCL>select chain_name from user_scheduler_chains;
CHAIN_NAME
------------------------------
TYGER_CHAIN
/* 创建chain step
为链TYGER_CHAIN 加入step ，能够一个一个的加入，也能够一下所有加入 */
TYGER@ORCL>
TYGER@ORCL>begin
dbms_scheduler.define_chain_step(
chain_name =>'TYGER_CHAIN',
step_name =>'CHAIN_STEP1',
program_name => 'CHAIN_P1');
end;
/
2 3 4 5 6 7
PL/SQL procedure successfully completed.
TYGER@ORCL>begin
dbms_scheduler.define_chain_step(
chain_name =>'TYGER_CHAIN',
step_name =>'CHAIN_STEP2',
program_name => 'chain_step2');
dbms_scheduler.define_chain_step(
chain_name =>'TYGER_CHAIN',
step_name =>'chain_step3',
program_name => 'CHAIN_P3');
end;
/
2 3 4 5 6 7 8 9 10 11
PL/SQL procedure successfully completed.
/* 查看创建的step 是否成功 */
TYGER@ORCL>col chain_name for a16
TYGER@ORCL>col step_name for a16
TYGER@ORCL>col program_name for a16
TYGER@ORCL>select chain_name,step_name,program_name
2 from user_scheduler_chain_steps;
CHAIN_NAME STEP_NAME PROGRAM_NAME
---------------- ---------------- ----------------
TYGER_CHAIN CHAIN_STEP1 CHAIN_P1
TYGER_CHAIN CHAIN_STEP2 CHAIN_P2
TYGER_CHAIN CHAIN_STEP3 CHAIN_P3
/* 为链创建规则 */
/* 规则：首先运行 chain_step1,成功完毕后运行 chain_step2 ,成功完毕后运行 chain_step3,成功完毕后，结束。 */
TYGER@ORCL>
TYGER@ORCL>begin
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'TYGER_CHAIN',
condition => 'true',
action => 'start CHAIN_STEP1',
rule_name => 'rule_01');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'TYGER_CHAIN',
condition => 'CHAIN_STEP1 SUCCEEDED',
action => 'start chain_step2',
rule_name => 'rule_02');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'TYGER_CHAIN',
condition => 'chain_step2 SUCCEEDED',
action => 'start chain_step3',
rule_name => 'rule_03');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'TYGER_CHAIN',
condition => 'chain_step3 SUCCEEDED',
action => 'END 0',
rule_name => 'rule_04');
END;
/
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
PL/SQL procedure successfully completed.
/* 启用链 tyger_chain */
TYGER@ORCL>exec dbms_scheduler.enable('TYGER_CHAIN');
PL/SQL procedure successfully completed.
手动执行链 tyger_chain
TYGER@ORCL>begin
DBMS_SCHEDULER.RUN_CHAIN (
chain_name =>'TYGER_CHAIN',
start_steps =>'CHAIN_STEP1');
end;
/
2 3 4 5 6
PL/SQL procedure successfully completed.
/* 验证
是不是依照我们设计的规则 tab1 -> tab2 -> tab3 ->exit 完毕的。
*/
TYGER@ORCL>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
TYGER@ORCL>select * from tab1;
ID CDATE SDATE
---------- ------------------- -------------------
1 2014-06-06 13:14:24 2014-05-06 13:14:24
TYGER@ORCL>select * from tab2;
ID CDATE SDATE
---------- ------------------- -------------------
2 2014-07-06 13:14:25 2014-05-06 13:14:25
TYGER@ORCL>select * from tab3;
ID CDATE SDATE
---------- ------------------- -------------------
3 2014-08-06 13:14:25 2014-05-06 13:14:25
TYGER@ORCL>
最新文章
- 移动WEB开发之viewport
- 读书笔记 --TCP :传输控制协议（二）
- pdo的工作方式与查错
- 百度的domain命令到底有用吗？
- GET和POST的区别，就是明信片和信封的区别
- java的版本区别、下载、配置
- 关于left join 和 inner join
- Cocos2d-android游戏引擎-介绍
- php 依赖注入容器
- 机器学习基石 1 The Learning Problem
- Linux系统初始化过程及运行级别简介
- day14 集合与函数
- js和jQuery的互相转换
- Java 8 并发性基础
- angularJs实现级联操作
- java8 流操作 好文网址
- kettle安装部署及远程执行
- 【C#】遍历List列表的同时，移除访问到的元素
- 20145211黄志远《网络对抗》Exp9 Web安全基础实践
- eclipse启动无响应，老是加载不了revert resources,或停留在Loading workbench状态
热门文章
- Jenkins 无法下载插件的解决办法
- AcpectJ注释方式配置AOP
- 关于其它模块的设计，有非常多须要自己去构建和完毕，在这里就简单地举几个样例来看看其它模块的设计。我们要做的就是有更改password模块，客户选择模块和关于本软件模块。更改password模块用来更改管理员的password，客户选择对话框模块用来选择已加入的客户，关于本软件模块用来说明客户管理系统的一些必要信息和制作人的信息。
- linux配置jdk失败
- 【VBA】获取模板保存的路径
- springMVC 前后台日期格式传值解决方式之二（共二） @InitBinder的使用
- DevOps开源工具的三种分类整理
- visual studio 2010 LNK1123解决方式
- BFS 和 DFS
- 不可忽略的apache 的 Keep Alive