오라클 잡 스케줄러 사용하기

 -- 오라클 10G 잡 스케줄러
 -- 스케줄러 잡 기능 실행을 위해 권한 부여(SYS 계정에서)
GRANT CREATE ANY JOB TO SCOTT;
 

-- 실행 잡의 실행 스케줄 생성(별도로 스케줄 생성 안하고 잡 생성시 지정 가능)

begin
   dbms_scheduler.create_schedule
   (        
      schedule_name   => 'EARLYWARNING_GRADE_SCHEDULE',
      start_date       => systimestamp,
      end_date        => null,
      repeat_interval => 'freq=daily;byhour=8,12,17,23;byminute=0;bysecond=0;',

      --repeat_interval => 'freq=daily;byhour=8,12;byminute=30;bysecond=0;', -- '매일 8:30,12:30 에 JOB 실행'
      comments      => '매일 8,12,17,23 정각에 JOB 실행'
); 
end;

-- 실행될 스케줄 잡 생성

begin

   dbms_scheduler.create_job
   (
      job_name    => 'EARLYWARNING_GRADE_JOB',
      job_type    => 'PLSQL_BLOCK',
      job_action  => 'BEGIN SP_EARLYWARNING_GRADE(); END;',
      schedule_name => 'EARLYWARNING_GRADE_SCHEDULE',
      comments      => '조기경보등급생성스케줄잡'
   );
end;
 

-- 이 후 실행을 위해 잡을 활성화
execute dbms_scheduler.enable('EARLYWARNING_GRADE_JOB');

 

-- 잡을 바로 실행

execute dbms_scheduler.run_job('EARLYWARNING_GRADE_JOB');

 

-- 잡을 삭제
begin
   dbms_scheduler.drop_job
   (
   job_name   => 'EARLYWARNING_GRADE_JOB';
   force      => false
   );
end;

 

-- 생성된 객체들은 다음과 같은 뷰를 통해 확인 가능(sys계정에 존재)         
select * from USER_SCHEDULER_SCHEDULES;

select * from USER_SCHEDULER_JOB_RUN_DETAILS;

select * from user_scheduler_jobs;

select * from user_scheduler_job_log;

 

-- create_schedule 생성시 지정한 repeat_interval을 대입하여 미리 예상 실행 시점 파악하기
set serveroutput on size 999999

declare
   L_start_date    TIMESTAMP;
   l_next_date     TIMESTAMP;
   l_return_date   TIMESTAMP;
begin
   l_start_date := trunc(SYSTIMESTAMP);
   l_return_date := l_start_date;
   for ctr in 1..10 loop
      dbms_scheduler.evaluate_calendar_string(
        'freq=daily;byhour=7,12,17,23;byminute=0;bysecond=0;',
         l_start_date, l_return_date, l_next_date
      );
      dbms_output.put_line('Next Run on : ' ||
          to_char(l_next_date,'yyyy/mm/dd hh24:mi:ss')
      );
      l_return_date := l_next_date;
   end loop;
end;
/

참조 : http://www.oradev.com/dbms_scheduler.jsp

Job scheduling from Oracle 10g with dbms_scheduler

In Oracle 10g the DBMS_JOB package is replaced by the DBMS_SCHEDULER package. The DBMS_JOB package is now depricated and in Oracle 10g it's on ly provided for backward compatibility. From Oracle 10g the DBMS_JOB package should not be used any more, because is could not exist in a future version of Oracle.

With DBMS_SCHEDULER Oracle procedures and functions can be executed. Also binary and shell-scripts can be scheduled.

Rights

If you have DBA rights you can do all the scheduling. For administering job scheduling you need the priviliges belonging to the SCHEDULER_ADMIN role. To create and run jobs in your own schedule you need the 'CREATE JOB' privilege.

With DBMS_JOB you needed to set an initialization parameter to start a job coordinator background process. With Oracle 10g DBMS_SCHEDULER this is not needed any more.

If you want to user resource plans and/or consumer groups you need to set a system parameter:
ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

Getting started quickly

To quickly get a job running, you can use code like this:

begin
  dbms_scheduler.create_job(
      job_name => 'DEMO_JOB_SCHEDULE'
     ,job_type => 'PLSQL_BLOCK'
     ,job_action => 'begin package.procedure(''param_value''); end; '
     ,start_date => '01/01/2006 02:00 AM'
     ,repeat_interval => 'FREQ=DAILY'
     ,enabled => TRUE
     ,comments => 'Demo for job schedule.');
end;
/

This schedules a pl/sql block to be executed daily starting 1/1/2006 02:00 AM.
You can schedule things like this, but DBMS_SCHEDULER can reuse components.

You can build a schedule using components like program, schedule, job, job class and window. We will now discuss these components in detail.

Program

The program component represents program-code that can be executed. This program code can have parameters. Code example

begin
   dbms_scheduler.create_program (
       program_name => 'DEMO_JOB_SCHEDULE'
      ,program_type => 'STORED_PROCEDURE'
      ,program_action => 'package.procedure'
      ,number_of_arguments => 1
      ,enabled => FALSE
      ,comments => 'Demo for job schedule.');
     
   dbms_scheduler.define_program_argument (
       program_name => 'DEMO_JOB_SCHEDULE'
      ,argument_position => 1
      ,argument_name => 'kol1'
      ,argument_type => 'VARCHAR2'
      ,default_value => 'default'
    );
    dbms_scheduler.enable(name => 'DEMO_JOB_SCHEDULE');
end;
/

The parameter program_type can have on e of the following values: 'PLSQL_BLOCK', 'STORED_PROCEDURE','EXECUTABLE'.
dbms_scheduler also allows to execute shell scripts (Windows: *.bat files) and executables.

Schedule

A schedule defines the frequence and date/time specifics of the start-time for the job.
example code

begin
  dbms_scheduler.create_schedule( 
      schedule_name => 'DEMO_SCHEDULE'
    , start_date =>  '01/01/2006 22:00:00'
    , repeat_interval => 'FREQ=WEEKLY'
    , comments => 'Weekly at 22:00');
END;
/

To drop the schedule:
begin
  dbms_scheduler.drop_schedule(
     schedule_name => 'DEMO_SCHEDULE'
     , force => TRUE );
end;
/

Calendar expresions can have on e of these values: 'Yearly','Monthly','Weekly','Daily','Hourly','Minutely','Secondely'

Job

A job defines when a specific task will be started. This can be done by assigning a program to on e or more schedules (or to a specific date/time). A job can belong to on ly 1 job class. Code example

begin
  dbms_scheduler.create_job(
       job_name => 'DEMO_JOB1'
     , program_name =>'DEMO_JOB_SCHEDULE'
     , schedule_name =>'DEMO_SCHEDULE'
     , enabled => FALSE
     , comments => 'Run demo program every week at 22:00');

   dbms_scheduler.set_job_argument_value(
        job_name => 'DEMO_JOB1'
      , argument_position => 1
      , argument_value => 'param1');

   dbms_scheduler.enable('DEMO_JOB1');

   commit;   
end;
/

Or start shell script

begin
   dbms_scheduler.create_job
   (
      job_name      => 'RUN_SHELL1',
      schedule_name => 'DEMO_SCHEDULE',
      job_type      => 'EXECUTABLE',
      job_action    => '/home/test/run_script.sh',
      enabled       => true,
      comments      => 'Run shell-script'
   );
end;
/

Monitoring job-scheduling

Jobs can be monitored using Oracle Enterprise Manager 10g. It's also possible to use a number of views that have been created in Oracle 10g. We will discuss some of these views here.

To show details on  job run:
select log_date
,      job_name
,      status
,      req_start_date
,      actual_start_date
,      run_duration
from   dba_scheduler_job_run_details
 
To show running jobs:
select job_name
,      session_id
,      running_instance
,      elapsed_time
,      cpu_used
from dba_scheduler_running_jobs;
 
To show job history:
 select log_date
 ,      job_name
 ,      status
 from dba_scheduler_job_log;

show all schedules:
select schedule_name, schedule_type, start_date, repeat_interval
from dba_scheduler_schedules;

show all jobs and their attributes:
select *
from dba_scheduler_jobs

show all program-objects and their attributes
select *
from dba_scheduler_programs;

show all program-arguments:
select *
from   dba_scheduler_program_args;

참조: http://blog.daum.net/won-bo/16839168