本讲内容主要包括
1.使用调度器简化管理任务
2.创建任务，项目和调度器
3.监控任务的执行
4.使用基于时间和基于事件的调度器
5.使用作业链
一:创建一个基于时间的任务和调度器
1. [oracle@rhel6 ~]$ cat /home/oracle/1.sh
2. #!/bin/sh
3. export ORACLE_BASE=/u01/app
4. export ORACLE_HOME=/u01/app/oracle
5. export ORACLE_SID=ora10g
6. export NLS_LANG=american_america.UTF8
7.
8. /u01/app/oracle/bin/sqlplus /nolog >> /home/oracle/1.txt < 9. conn /as sysdba 10. select sysdate from dual; 11. exit 12. EOF 1. [oracle@rhel6 ~]$ tail -f 1.txt 2. Copyright (c) 1982, 2005, Oracle. All rights reserved. 3. 4. SQL> Connected. 5. SQL> 6. SYSDATE 7. --------------- 8. 09-AUG-11 9. 10. SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production 11. With the Partitioning, OLAP and Data Mining options 二：创建基于事件的任务和调度器 1. [oracle@rhel6 ~]$ sqlplus /nolog 2. SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 9 11:38:06 2011 3. Copyright (c) 1982, 2005, Oracle. All rights reserved. 4. SQL> conn /as sysdba 5. Connected. 6. SQL> grant create type to hr; 7. Grant succeeded. 8. SQL> grant aq_administrator_role to hr; 9. Grant succeeded. 10. SQL> grant create job to hr; 11. Grant succeeded 12. 13. SQL> conn hr/hr 14. Connected. 15. SQL> CREATE TABLE event_schedule ( 16. 2 id NUMBER(10) NOT NULL, 17. 3 created_date DATE NOT NULL, 18. 4 CONSTRAINT event_schedule_pk PRIMARY KEY (id) 19. 5* )； 20. Table created. 21. 22. SQL> CREATE SEQUENCE seq_event; 23. Sequence created. 24. 25. SQL> CREATE OR REPLACE TYPE t_event_queue AS OBJECT ( event_name VARCHAR2(30) ); 26. Type created. 27. 28. SQL> BEGIN 29. 2 -- Create a queue table to hold the event queue. 30. 3 DBMS_AQADM.create_queue_table( 31. 4 queue_table => 'event_queue_tab', 32. 5 queue_payload_type => 't_event_queue', 33. 6 multiple_consumers => TRUE, 34. 7 comment => 'Queue Table For Event Messages'); 35. 8 -- Create the event queue. 36. 9 DBMS_AQADM.create_queue ( 37. 10 queue_name => 'event_queue', 38. 11 queue_table => 'event_queue_tab'); 39. 12 -- Start the event queue. 40. 13 DBMS_AQADM.start_queue (queue_name => 'event_queue'); 41. 14* END; 42. PL/SQL procedure successfully completed 43. 44. SQL> BEGIN 45. 2 DBMS_SCHEDULER.create_job ( 46. 3 job_name => 'event_based_job', 47. 4 job_type => 'PLSQL_BLOCK', 48. 5 job_action => 'BEGIN 49. 6 INSERT INTO event_schedule (id, created_date) 50. 7 VALUES (seq_event.NEXTVAL, SYSDATE); 51. 8 COMMIT; 52. 9 END;', 53. 10 start_date => SYSTIMESTAMP, 54. 11 event_condition => 'tab.user_data.event_name = ''give_me_a_job''', 55. 12 queue_spec => 'event_queue', 56. 13 enabled => TRUE); 57. 14* END; 58. PL/SQL procedure successfully completed. 59. 60. SQL> select * from event_schedule; 61. no rows selected 62. 63. SQL> DECLARE 6
