Đỉnh NGUYỄN

life's a journey not a destination

Một số ví dụ về Oracle Job Scheduler

4 Comments


DBMS_SCHEDULER là 1 package của Oracle (có từ version 10) cung cấp các procedure giúp tạo job.

  Tạo Oracle Job gồm 3 bước:

  1. Tạo time schedule – dbms_scheduler.create_schedule
  2. Tạo program declaration – dbms_scheduler.create program
  3. Tạo job (conflation) – dbms_scheduler.create_job

Lưu ý: login vào với vai trò sys và gán quyền cho user cần tạo job:

SQL> conn / as sysdba

GRANT create any directory TO fhr;
GRANT create procedure TO fhr;
GRANT create table TO fhr;

GRANT create job TO fhr;
GRANT manage scheduler TO fhr;

1. Tạo time schedule

begin
-- daily from Monday to Sunday at 22:00 (10:00 p.m.)
dbms_scheduler.create_schedule
(schedule_name => 'INTERVAL_DAILY_2200',
 start_date=> trunc(sysdate)+18/24, -- start today 18:00 (06:00 p.m.)
 repeat_interval=> 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=22;',
 comments=>'Runtime: Every day (Mon-Sun) at 22:00 o clock');

-- run every hour, every day
dbms_scheduler.create_schedule(  
schedule_name  => 'INTERVAL_EVERY_HOUR',  
  start_date    => trunc(sysdate)+18/24,  
  repeat_interval => 'freq=HOURLY;interval=1',  
  comments     => 'Runtime: Every day every hour');  

-- run every 5 minute, every day
dbms_scheduler.create_schedule(
schedule_name  => 'INTERVAL_EVERY_5_MINUTES',
  start_date    => trunc(sysdate)+18/24,
  repeat_interval => 'freq=MINUTELY;interval=5',
  comments     => 'Runtime: Every day all 5 minutes');

-- run every minute, every day
dbms_scheduler.create_schedule(
schedule_name  => 'INTERVAL_EVERY_MINUTE',
  start_date    => trunc(sysdate)+18/24,
  repeat_interval => 'freq=MINUTELY;interval=1',
  comments     => 'Runtime: Every day every minute');

-- run every Sunday at 18:00 (06:00 p.m.)
dbms_scheduler.create_schedule
(schedule_name => 'INTERVAL_EVERY_SUN_1800',
 start_date=> trunc(sysdate)+18/24,
 repeat_interval=> 'FREQ=DAILY; BYDAY=SUN; BYHOUR=18;',
 comments=>'Runtime: Run at 6pm every Sunday');
end;

2. Tạo program declaration

begin
-- Call a procedure of a database package
dbms_scheduler.create_program
(program_name=> 'PROG_COLLECT_SESS_DATA',
 program_type=> 'STORED_PROCEDURE',
 program_action=> 'pkg_collect_data.prc_session_data',
 enabled=>true,
 comments=>'Procedure to collect session information'
 );
end;

3. Tạo Job

begin
-- Connect both dbms_scheduler parts by creating the final job
dbms_scheduler.create_job
 (job_name => 'JOB_COLLECT_SESS_DATA',
  program_name=> 'PROG_COLLECT_SESS_DATA',
  schedule_name=>'INTERVAL_EVERY_5_MINUTES',
  enabled=>true,
  auto_drop=>false,
  comments=>'Job to collect data about session values every 5 minutes');
end;

4. Thay đổi properties của phần Time scheduler

 

begin
-- change start time
DBMS_SCHEDULER.SET_ATTRIBUTE(
   name => 'INTERVAL_EVERY_5_MINUTES',
   attribute => 'start_date',
   value => to_date('22.06.2009 12:15','dd.mm.yyyy hh24:mi')
);

-- change repeat interval
DBMS_SCHEDULER.SET_ATTRIBUTE(
   name => 'INTERVAL_EVERY_MINUTE',
   attribute => 'repeat_interval',
   value => 'freq=MINUTELY;interval=2'
); 
end;

5. Run job

begin
dbms_scheduler.run_job('JOB_COLLECT_SESS_DATA',TRUE);
end;

6. Restart job

begin
dbms_scheduler.disable('JOB_COLLECT_INST_INFO');
dbms_scheduler.enable('JOB_COLLECT_INST_INFO');
end;

7. Job status

-- All jobs
select * from user_scheduler_jobs;

-- Get information to job
select * from user_scheduler_job_log order by log_date desc;

-- Show details on job run
select * from user_scheduler_job_run_details;

8. Alternative Job status

-- View all running jobs, Run the following query from SQL*Plus
SELECT a.sid, c.serial#, a.job, a.failures, to_char(a.this_date, 'mm/dd/yyyy hh:mi pm') startdatetime, b.what
FROM dba_jobs_running a, dba_jobs b, v$session c
WHERE a.job = b.job AND a.sid = c.sid order by a.this_date;

-- View all jobs, Run the following query from SQL*Plus
SELECT job, to_char(last_date, 'mm/dd/yyyy hh:mi pm') lastdate, to_char(next_date, 'mm/dd/yyyy hh:mi pm') nextdate, failures, broken, what 
FROM dba_jobs 
ORDER BY next_date;

9. Killing a running job

Ý hay là khi muốn kill 1 job mà không muốn nó tự restart lại là hãy đánh dấu broken. Chạy lệnh sau từ SQL*Plus:

-- Võìi job_id lâìy týÌ câu truy vâìn “View all running jobs”.
execute dbms_job.broken(job_id, true);

Sau đó, kill session bằng 1 trong 2 cách:

#1: (the best / fast way to kill a session)

-- Find the thread you want to kill
SELECT sid, spid as thread, osuser, s.program
FROM sys.v_$process p, sys.v_$session s
WHERE p.addr = s.paddr;

-- Run the following for Oracle on Linux, sid is the name given to the Oracle Instance 
-- (the name given in the TNSnames.ora fle)
orakill sid thread

#2:

-- Run the following query from SQL*Plus
-- sid and serial# came from the "View all running jobs" query. 
-- The problem with this option is it can sometimes take a long time to kill the session
alter system kill session 'sid, serial#'

Để remove hoàn toàn job, chạy lệnh:

-- job_id is obtained from the "View all running jobs" query.
execute dbms_job.remove(job_id)

Nếu chỉ muốn stop job để fix chứ không muốn remove hoàn toàn, hãy fix job và restart bằng lệnh:

-- job_id is obtained from the "View all running jobs" query
execute dbms_job.broken(job_id, false);

Ghi chú: Khi nào job trở thành ‘broken’?

  • Oracle thực hiện job thành công sau 16 lần cố gắng thực hiện (có lỗi khi thực hiện job). hoặc
  • Người dùng đánh dấu job là ‘broken’, dùng proc DBMS_JOB.BROKEN.
    Khi 1 job được đánh dấu là ‘broken’. Oracle sẽ không cố gắng thực hiện job nữa đến khi job được đánh dấu không còn ‘broken’ hoặc bị ép thực thi bằng lời gọi DBMS_JOB.RUN.

10. Tạo job mà không cần time scheduler và program declaration

begin
dbms_scheduler.create_job (
  job_name            => 'TEST_JOB',
  job_type            => 'PLSQL_BLOCK',
  job_action          => 'begin /* some process code */ commit; end;',
  number_of_arguments => 0,
  start_date          => sysdate +1/24/59, -- sysdate + 1 minute
  job_class           => 'DEFAULT_JOB_CLASS',  -- Normal Group
  enabled             => TRUE,
  auto_drop           => TRUE,
  comments            => 'Testrun');
end;

Advertisements

Author: dinhnn

Senior software developer, a technical leader. You can be reached at via email to dinhnguyenngoc@gmail.com, via my blog at dinhnguyenngoc.wordpress.com, and on Twitter @dinhnguyenngoc.

4 thoughts on “Một số ví dụ về Oracle Job Scheduler

  1. whoah this weblog is magnificent i like reading your
    articles. Stay up the great work! You already know, lots of individuals
    are hunting around for this info, you could help them
    greatly.

  2. với các procedure có tham số thì đưa vào job thé nào vậy anh?

    • Chào bạn,

      Với các procedure có tham số, bạn có thể dùng dbms_scheduler.create_program với program_type=’PLSQL_BLOCK’ hoặc viết như sau:

      begin
      — Call a procedure of a database package
      dbms_scheduler.create_program
      (program_name => ‘PROG_COLLECT_SESS_DATA’,
      program_type => ‘STORED_PROCEDURE’,
      program_action => ‘pkg_collect_data.prc_session_data’,
      number_of_arguments => 2,
      enabled => true,
      comments => ‘Procedure to collect session information’
      );
      end;

      BEGIN
      dbms_scheduler.define_program_argument(
      program_name => ‘PROG_COLLECT_SESS_DATA’,
      argument_position => 1,
      argument_type => ‘VARCHAR2’,
      default_value => ‘BIZ’);

      dbms_scheduler.define_program_argument(
      program_name => ‘PROG_COLLECT_SESS_DATA’,
      argument_position => 2,
      argument_type => ‘NUMBER’,
      default_value => 2015);
      END;

  3. good job. thank you so much.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s