[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 eva luation 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',
prog |