课程第6次

secoug

Notice: This blog is written by Bo Tang.



job1:







本地内部作业

-----------------------------------------------------------------
job2:
本地外部作业:

1. select  * from dba_scheduler_credentials;
   
2. 
   
3. begin
   
4.    dbms_scheduler.create_credential(credential_name => 'credential1',
   
5.    username => 'oracle',
   
6.    password => 'oracle');
   
7. end;
   
8. 
   
9. grant execute on credential1 to hr;
   
10. 
    
11. select * from dba_tab_privs tp where tp.grantee='HR';
    
12. 
    
13. grant create external job to hr;
    
14. 
    
15. select * from dba_sys_privs  sp where sp.grantee='HR';
    

复制代码

job2.sh:

1. #!/bin/sh
   
2. export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
   
3. export ORACLE_SID=orcl
   
4. export ORACLE_BASE=/u01/app/oracle
   
5. 
   
6. /u01/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus /nolog >> /home/oracle/job2a.log <<EOF
   
7. conn hr/oracle_4U
   
8. alter session set nls_date_format='yyyy-mm-dd:hh24:mi:ss';
   
9. select  sysdate from dual;
   
10. exit
    
11. EOF
    
12. 
    
13. date +%Y%m%d-%H%M%S >> /home/oracle/job2b.log

复制代码

1. BEGIN
   
2. DBMS_SCHEDULER.CREATE_PROGRAM(
   
3. program_name=>'"HR"."PROGRAM2"',
   
4. program_action=>'/home/oracle/job2.sh',
   
5. program_type=>'EXECUTABLE',
   
6. number_of_arguments=>0,
   
7. comments=>'',
   
8. enabled=>TRUE);
   
9. END;

复制代码

1. BEGIN
   
2. sys.dbms_scheduler.create_schedule(
   
3. repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
   
4. start_date => systimestamp at time zone 'Asia/Shanghai',
   
5. schedule_name => '"HR"."SCHEDULE2"');
   
6. END;

复制代码

1. 
   
2. 
   
3. BEGIN
   
4. sys.dbms_scheduler.create_job(
   
5. job_name => '"HR"."JOB2"',
   
6. program_name => '"HR"."PROGRAM2"',
   
7. schedule_name => '"HR"."SCHEDULE2"',
   
8. job_class => '"DEFAULT_JOB_CLASS"',
   
9. auto_drop => FALSE,
   
10. enabled => FALSE);
    
11. DBMS_SCHEDULER.SET_ATTRIBUTE(name => '"HR"."JOB2"', attribute => 'credential_name', value => '"SYS"."CREDENTIAL1"' );
    
12. sys.dbms_scheduler.enable( '"HR"."JOB2"' );
    
13. END;

复制代码

------------------------------------------------
schedule3系列：

1. 
   
2. declare
   
3.    v_1  timestamp;
   
4. begin
   
5.   for i in 1..5
   
6.    loop
   
7.     DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (  
   
8.           calendar_string=>'freq=YEARLY;bydate=1007,1008,1101'   ,
   
9.    start_date => sysdate,
   
10.    return_date_after  => v_1,
    
11.    next_run_date    => v_1 );
    
12.   dbms_output.put_line(to_char(v_1,'YYYY-MM-DD:HH24:MI:SS'));
    
13. end loop;
    
14. end
    

复制代码

1. declare
   
2. v_1 timestamp;
   
3. begin
   
4. for i in 1..5
   
5. loop
   
6. DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (
   
7. calendar_string=>'freq=YEARLY;byday=MON,TUE,WED,THU,FRI;BYHOUR=14;BYMINUTE=10;BYSECOND=0;exclude=HR.SCHEDULE3' ,
   
8. start_date => sysdate,
   
9. return_date_after => v_1,
   
10. next_run_date => v_1 );
    
11. dbms_output.put_line(to_char(v_1,'YYYY-MM-DD:HH24:MI:SS'));
    
12. end loop;
    
13. end;

复制代码

有名字的schedule不能带有“时”、“分”和“秒”

1. BEGIN
   
2. sys.dbms_scheduler.create_schedule(
   
3. repeat_interval => 'freq=YEARLY;byday=MON,TUE,WED,THU,FRI;BYHOUR=14;BYMINUTE=10;BYSECOND=0;exclude=HR.SCHEDULE3',
   
4. start_date => systimestamp at time zone 'Asia/Shanghai',
   
5. schedule_name => '"HR"."SCHEDULE31"');
   
6. END;

复制代码

-----------
“Oracle Advanced Queuing by Example”
-----------

1. select * from dba_queues;
   
2. 
   
3. 
   
4. CREATE OR REPLACE TYPE t_event_queue_payload AS OBJECT (
   
5.   event_name  VARCHAR2(30)
   
6. );
   
7. 
   
8. 
   
9. grant execute on t_event_queue_payload to hr;
   
10. 
    
11. 
    
12. select * from dba_tab_privs tp where tp.grantee='HR';
    
13. 
    
14. 
    
15. BEGIN
    
16.   -- Create a queue table to hold the event queue.
    
17.   DBMS_AQADM.create_queue_table(
    
18.     queue_table        => 'event_queue_tab',
    
19.     queue_payload_type => 't_event_queue_payload',
    
20.     multiple_consumers => TRUE,
    
21.     comment            => 'Queue Table For Event Messages');
    
22.   -- Create the event queue.
    
23.   DBMS_AQADM.create_queue (
    
24.     queue_name  => 'event_queue',
    
25.     queue_table => 'event_queue_tab');
    
26.   -- Start the event queue.
    
27.   DBMS_AQADM.start_queue (queue_name => 'event_queue');
    
28. END;
    
29. 
    
30. select * from dba_queues q where q.name='EVENT_QUEUE';
    
31. 
    
32. 
    
33. begin
    
34.    dbms_aqadm.grant_queue_privilege(privilege => 'ENQUEUE',queue_name => 'event_queue',grantee => 'HR');
    
35. end;
    
36.    
    
37. begin
    
38.    dbms_aqadm.grant_queue_privilege(privilege => 'DEQUEUE',queue_name => 'event_queue',grantee => 'HR');
    
39. end;
    
40. 
    
41. 
    
42. select * from dba_tab_privs tp where tp.grantee='HR';
    
43. 
    
44. 
    
45. grant execute on dbms_aq to hr;
    
46. 
    
47. --------------------------------------
    
48. 
    
49. CREATE TABLE hr.scheduler_test (
    
50.   id            NUMBER(10)    NOT NULL,
    
51.   created_date  DATE          NOT NULL,
    
52.   CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
    
53. );
    
54. 
    
55. select  * from hr.scheduler_test;
    
56. CREATE SEQUENCE hr.scheduler_test_seq;
    

复制代码

Program4：

1. BEGIN
   
2. sys.dbms_scheduler.create_job(
   
3. job_name => '"HR"."JOB4"',
   
4. program_name => '"HR"."PROGRAM4"',
   
5. schedule_name => '"HR"."SCHEDULE4"',
   
6. job_class => '"DEFAULT_JOB_CLASS"',
   
7. auto_drop => FALSE,
   
8. enabled => TRUE);
   
9. END;

复制代码

---------------------------------
模拟信号发生器：
HR：

1. DECLARE
   
2.   l_enqueue_options     DBMS_AQ.enqueue_options_t;
   
3.   l_message_properties  DBMS_AQ.message_properties_t;
   
4.   l_message_handle      RAW(16);
   
5.   l_queue_msg           sys.t_event_queue_payload;
   
6. BEGIN
   
7.   l_queue_msg := sys.t_event_queue_payload('Chemical_Wrong');
   
8. 
   
9.   DBMS_AQ.enqueue(queue_name          => 'sys.event_queue',
   
10.                   enqueue_options     => l_enqueue_options,
    
11.                   message_properties  => l_message_properties,
    
12.                   payload             => l_queue_msg,
    
13.                   msgid               => l_message_handle);
    
14.   COMMIT;
    
15. END;
    

复制代码