课程第53次：2016-07-20星期三

botang · 发表于 2016-7-20 23:28:35

本帖最后由 botang 于 2016-7-21 09:16 编辑

课程第53次：2016-07-20星期三
【继续1Z0-053的第17章】Scheduler

参考别期的帖子：
https://www.botangdb.com/forum.php?mod=viewthread&tid=266&extra=page%3D1

昨天晚上的随堂SQL：

job5.sql：

select * from dba_scheduler_global_attribute;
begin
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
('email_server','station90.example.com');
end;
select * from dba_scheduler_global_attribute;
begin
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
('email_sender','oracle@example.com');
end;
select * from dba_scheduler_global_attribute;
begin
DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
job_name =>'HR.JOB5',
events =>'job_succeeded,job_completed',
recipients =>'oracle@example.com');
end;
---
select * from dba_scheduler_notifications;
---
begin
DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION (
job_name =>'HR.JOB5');
end;
----
select * from dba_scheduler_notifications;
---
begin
DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
job_name =>'HR.JOB5',
events => 'job_failed',
recipients =>'oracle@example.com');
end;
select * from dba_scheduler_notifications;
----

复制代码

job6.sql：

--hr:
DROP TABLE scheduler_test;
DROP SEQUENCE scheduler_test_seq;
CREATE TABLE scheduler_test (
id NUMBER(10) NOT NULL,
description VARCHAR2(20) NOT NULL,
created_date DATE NOT NULL,
CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
);
CREATE SEQUENCE scheduler_test_seq;
BEGIN
DBMS_SCHEDULER.create_program (
program_name => 'test_program_1',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN
INSERT INTO scheduler_test (id, description, created_date)
VALUES (scheduler_test_seq.NEXTVAL, ''test_program_1'', SYSDATE);
COMMIT;
END;',
enabled => TRUE,
comments => 'Step2');
DBMS_SCHEDULER.create_program (
program_name => 'test_program_2',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN
INSERT INTO scheduler_test (id, description, created_date)
VALUES (scheduler_test_seq.NEXTVAL, ''test_program_2'', SYSDATE);
COMMIT;
END;',
enabled => TRUE,
comments => 'Step3');
DBMS_SCHEDULER.create_program (
program_name => 'test_program_3',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN
INSERT INTO scheduler_test (id, description, created_date)
VALUES (scheduler_test_seq.NEXTVAL, ''test_program_3'', SYSDATE);
COMMIT;
END;',
enabled => TRUE,
comments => 'Step4');
END;
--SYS
em做
如果HR本人，需要以下权限
BEGIN
DBMS_RULE_ADM.grant_system_privilege(
privilege => DBMS_RULE_ADM.create_rule_set_obj,
grantee => 'HR',
grant_option => FALSE);
DBMS_RULE_ADM.grant_system_privilege(
privilege => DBMS_RULE_ADM.create_evaluation_context_obj,
grantee => 'HR',
grant_option => FALSE);
DBMS_RULE_ADM.grant_system_privilege(
privilege => DBMS_RULE_ADM.create_rule_obj,
grantee => 'HR',
grant_option => FALSE);
END;
另外：
analyze_chain：
dbms_scheduler.analyze_chain(chain_name => ,rules => ,steps => ,step_pairs => );
evaluate_running_chain：
dbms_scheduler.evaluate_running_chain(job_name => );
规则制定：
每一条规则都要有“condition”和“action”。
如果condition为TRUE, the action执行。 Conditions are usually based on the outcome of one or more previous steps.
A condition accepts Boolean and numeric integer values in an expression. The entire expression must evaluate to a Boolean value.
The simplified syntax of a chain condition is as follows:
'factor|NOT(factor)[AND|OR factor]'
factor:
stepname ERROR_CODE number|[NOT]step_condition
When creating a rule condition using the simplified syntax:
You specify one or more factors, and a Boolean operator (AND, OR, or NOT).
A factor can be either a simple Boolean value (TRUE or FALSE) or a chain condition. A chain condition describes the condition of another step in the job chain. You can use the following to describe the chain condition:
The current state of the chain step:
SUCCEEDED
FAILED
STOPPED
COMPLETED
The error code returned by the chain step. The error is a numeric value, and can be:
Evaluated within a numeric clause
Compared to a list of values using an IN clause
You can use negative factors, by enclosing the factor in parentheses and prefixing the factor with the NOT operator.
Examples:
'step1 SUCCEEDED AND step2 ERROR_CODE = 3'
'TRUE'
'step3 NOT COMPLETED AND NOT (step1 SUCCEEDED)'
'step2 ERROR_CODE NOT IN (1,2,3)'
You can also refer to attributes of chain steps of the chain (this is called bind-variable syntax). The syntax is as follows:
STEP_NAME.ATTRIBUTE
Possible attributes are: completed, state, start_date, end_date, error_code, and duration.
Possible values for the state attribute include: 'NOT_STARTED', 'SCHEDULED', 'RUNNING', 'PAUSED', 'SUCCEEDED', 'FAILED', and 'STOPPED'.
If a step is in the state 'SUCCEEDED', 'FAILED', or 'STOPPED', its completed attribute is set to 'TRUE'; otherwise, completed is 'FALSE'.
Some examples of the bind variable syntax are:
':step1.state=''SUCCEEDED'' and :step2.error_code=3'
'1=1'
':step3.state != ''COMPLETED'''
':step2.error_code not in (1,2,3)'
':step1.state = ''NOT_STARTED'''
The rule action specifies what is to be done as a result of the rule being triggered. A typical action is to run a specified step. Possible actions include:
START step_1[,step_2...]
STOP step_1[,step_2...]
END [{end_value | step_name.error_code}]
When the job starts and at the end of each step, all rules are evaluated to see what action or actions occur next. You can also configure rules to be evaluated at regular intervals by using the EVALUATION_INTERVAL attribute of the chain.
You add a rule to a chain with the DEFINE_CHAIN_RULE procedure:
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
CHAIN_NAME => 'bulk_load_chain',
CONDITION => 'TRUE', -- starting step
ACTION => 'START load_data_evt,stop_when_disk_full_evt',
Rule_Name => 'dataload_rule1',
COMMENTS => 'start the chain');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
CHAIN_NAME => 'bulk_load_chain',
CONDITION => 'load_data_evt COMPLETED',
ACTION => 'START do_bulk_load',
RULE_NAME => 'dataload_rule2');
END;
又比如：
BEGIN
DBMS_SCHEDULER.define_chain_rule (
chain_name => 'test_chain_1',
condition => 'TRUE',
action => 'START chain_step_1',
rule_name => 'chain_rule_1',
comments => 'First link in the chain.');
DBMS_SCHEDULER.define_chain_rule (
chain_name => 'test_chain_1',
condition => 'chain_step_1 completed',
action => 'START chain_step_2',
rule_name => 'chain_rule_2',
comments => 'Second link in the chain.');
DBMS_SCHEDULER.define_chain_rule (
chain_name => 'test_chain_1',
condition => 'chain_step_2 completed',
action => 'START chain_step_3',
rule_name => 'chain_rule_3',
comments => 'Third link in the chain.');
DBMS_SCHEDULER.define_chain_rule (
chain_name => 'test_chain_1',
condition => 'chain_step_3 completed',
action => 'END',
rule_name => 'chain_rule_4',
comments => 'End of the chain.');
END;
/
SET LINESIZE 200
COLUMN owner FORMAT A10
COLUMN chain_name FORMAT A15
COLUMN rule_owner FORMAT A10
COLUMN rule_name FORMAT A15
COLUMN condition FORMAT A25
COLUMN action FORMAT A20
COLUMN comments FORMAT A25
SELECT owner,
chain_name,
rule_owner,
rule_name,
condition,
action,
comments
FROM dba_scheduler_chain_rules
ORDER BY owner, chain_name, rule_owner, rule_name;
/
SET LINESIZE 200
COLUMN owner FORMAT A10
COLUMN job_name FORMAT A20
COLUMN chain_owner FORMAT A10
COLUMN chain_name FORMAT A15
COLUMN step_name FORMAT A25
SELECT owner,
job_name,
chain_owner,
chain_name,
step_name,
state
FROM dba_scheduler_running_chains
ORDER BY owner, job_name, chain_name, step_name;
BEGIN
DBMS_SCHEDULER.run_chain (
chain_name => 'test_chain_1',
job_name => 'test_chain_1_run_job',
start_steps => 'chain_step_1, chain_step_2, chain_step_3');
END;
/
/*
EXEC DBMS_SCHEDULER.drop_job(job_name => 'test_chain_1_job');
EXEC DBMS_SCHEDULER.drop_chain (chain_name => 'test_chain_1');
EXEC DBMS_SCHEDULER.drop_program (program_name => 'test_program_1');
EXEC DBMS_SCHEDULER.drop_program (program_name => 'test_program_2');
EXEC DBMS_SCHEDULER.drop_program (program_name => 'test_program_3');
DROP TABLE scheduler_test;
DROP SEQUENCE scheduler_test_seq;
PURGE RECYCLEBIN;
*/
--hr:
DECLARE
l_enqueue_options DBMS_AQ.enqueue_options_t;
l_message_properties DBMS_AQ.message_properties_t;
l_message_handle RAW(16);
l_queue_msg sys.t_event_queue_payload;
BEGIN
l_queue_msg := sys.t_event_queue_payload('give_me_a_prod');
DBMS_AQ.enqueue(queue_name => 'sys.event_queue',
enqueue_options => l_enqueue_options,
message_properties => l_message_properties,
payload => l_queue_msg,
msgid => l_message_handle);
COMMIT;
END;
select * from scheduler_test order by id;

复制代码

job7.sql：

select * from scheduler_test;
DROP TABLE scheduler_test;
DROP SEQUENCE scheduler_test_seq;
---
CREATE TABLE scheduler_test (
id NUMBER(10) NOT NULL,
description VARCHAR2(20) NOT NULL,
created_date DATE NOT NULL,
CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
);
CREATE SEQUENCE scheduler_test_seq;
----
BEGIN
DBMS_SCHEDULER.create_program (
program_name => 'test_program_1',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN
INSERT INTO scheduler_test (id, description, created_date)
VALUES (scheduler_test_seq.NEXTVAL, ''test_program_1'', SYSDATE);
COMMIT;
END;',
enabled => TRUE,
comments => 'Step2');
DBMS_SCHEDULER.create_program (
program_name => 'test_program_2',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN
INSERT INTO scheduler_test (id, description, created_date)
VALUES (scheduler_test_seq.NEXTVAL, ''test_program_2'', SYSDATE);
COMMIT;
END;',
enabled => TRUE,
comments => 'Step3');
DBMS_SCHEDULER.create_program (
program_name => 'test_program_3',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN
INSERT INTO scheduler_test (id, description, created_date)
VALUES (scheduler_test_seq.NEXTVAL, ''test_program_3'', SYSDATE);
COMMIT;
END;',
enabled => TRUE,
comments => 'Step4');
END;
---
select * from user_scheduler_chain_rules;
---
DECLARE
l_enqueue_options DBMS_AQ.enqueue_options_t;
l_message_properties DBMS_AQ.message_properties_t;
l_message_handle RAW(16);
l_queue_msg sys.t_event_queue_payload;
BEGIN
l_queue_msg := sys.t_event_queue_payload('give_me_a_prod');
DBMS_AQ.enqueue(queue_name => 'sys.event_queue',
enqueue_options => l_enqueue_options,
message_properties => l_message_properties,
payload => l_queue_msg,
msgid => l_message_handle);
COMMIT;
END;
select * from scheduler_test order by id;
BEGIN
DBMS_SCHEDULER.run_chain (
chain_name => 'chain1',
job_name => 'chain1_test_job',
start_steps => 'STEP2, STEP4');
END;
----
select s.USERNAME,s.RESOURCE_CONSUMER_GROUP
from v_$session s where username='HR';
grant execute on jobclass1 to hr;

复制代码