[10g] DBMS_SCHEDULER 사용 예제

/********************************************************************************************
-- Title : [10g] DBMS_SCHEDULER 사용 예제(.sh 포함)
-- Reference : orapybubu.blog.me
-- Key word : 스케쥴 스케쥴러 JOB 자동화 dbms scheduler create job shell 쉘
********************************************************************************************/

-- JOB 실패시 문제가 있단다. 오류 발생 후 스케쥴이 변경된다나 뭐라나..ㅡ.ㅡ;;
    그래서 누군가는 crontab을 강추하던데... 얼쑤~!!
-- DBMS_SCHEDULER의 구성요소
   ㅇ PROGRAM  : 스케줄러에 의해 실행될 DB 프로그램(PL/SQL, C, JAVA 등)
   ㅇ JOB            : 스케줄러에 등록된 작업 유형
   ㅇ SCHEDULE : 등록된 작업의 작업 일정(언제, 주기 등)
   ㅇ WINDOW     : 수행될 작업에 할당될 시스템 자원에 대한 정보


/****************************************
-- JOB 만들기 : 스케줄러에 등록된 작업 유형
****************************************/
-- PL/SQL
BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'oe.my_job1',
   job_type             => 'PLSQL_BLOCK',
   job_action           => 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(''oe'',
                            ''sales''); END;',
   start_date           => '15-JUL-03 1.00.00AM US/Pacific',
   repeat_interval      => 'FREQ=DAILY',
   end_date             => '15-SEP-03 1.00.00AM US/Pacific',
   enabled              =>  TRUE,
   comments             => 'Gather table statistics');
END;
/

-- PROGRAM
BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
   job_name          =>  'my_new_job1',
   program_name      =>  'my_saved_program',
   repeat_interval   =>  'FREQ=DAILY;BYHOUR=12',
   comments          =>  'Daily at noon');
END;
/

-- PL/SQL
BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
   job_name                 =>  'my_new_job2',
   job_type                 =>  'PLSQL_BLOCK',
   job_action               =>  'BEGIN SALES_PKG.UPDATE_SALES_SUMMARY; END;',
   schedule_name            =>  'my_saved_schedule');
END;
/

BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
   job_name            =>  'my_new_job3',
   program_name        =>  'my_saved_program1',
   schedule_name       =>  'my_saved_schedule1');
END;
/

BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
   job_name            =>  'my_job',
   program_name        =>  'my_program',
   event_condition     =>  'tab.user_data.event_type = ''CARD_SWIPE''',
   queue_spec          =>  'entry_events_q, entry_agent1',
   enabled             =>  TRUE,
   comments            =>  'Start job when someone swipes a badge');
END;
/

BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
   job_name            =>  'my_job',
   program_name        =>  'my_program',
   schedule_name       =>  'entry_events_schedule',
   enabled             =>  TRUE,
   comments            =>  'Start job when someone swipes a badge');
END;
/ 


/****************************************
-- CREATE_JOB을 이용한 OS SHELL 사용
****************************************/
BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
   job_name  =>  'tg_job',
   job_type  => 'EXECUTABLE',
   job_action  =>  'home/oracle/mytest.sh',
   start_date  =>  SYSTIMESTAMP,
   repeat_interval  =>  'freq=MINUTELY',
   end_date  =>  NULL,
   enabled  =>  TRUE,
   comments  => 'This scripts specifies usage for OS SHELL);
END;
/ 


/****************************************
-- PROGRAM 만들기
****************************************/
BEGIN
   DBMS_SCHEDULER.CREATE_PROGRAM (
   program_name          => 'oe.my_program1',
   program_type          => 'PLSQL_BLOCK',
   program_action        => 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(''oe'',
                             ''sales''); END;',
   number_of_arguments   => 0,
   enabled               => TRUE,
   comments              => 'My comments here');
END;
/


/****************************************
-- SCHEDULE(Time-Based) 만들기
****************************************/
BEGIN
   DBMS_SCHEDULER.CREATE_SCHEDULE (
   schedule_name     => 'my_stats_schedule',
   start_date        => SYSTIMESTAMP,
   end_date          => SYSTIMESTAMP + INTERVAL '30' day,
   repeat_interval   => 'FREQ=HOURLY; INTERVAL=4',
   comments          => 'Every 4 hours');
END;
/


/****************************************
-- SCHEDULE(Event-Based) 만들기
****************************************/
BEGIN
   DBMS_SCHEDULER.CREATE_EVENT_SCHEDULE (
   schedule_name     =>  'entry_events_schedule',
   start_date        =>  SYSTIMESTAMP,
   event_condition   =>  'tab.user_data.event_type = ''CARD_SWIPE''',
   queue_spec        =>  'entry_events_q, entry_agent1');
END;
/