时间戳

首页 > 解决方案 > 在 ORACLE 中创建作业

oracle - 在 ORACLE 中创建作业

问题描述

我必须定期创建工作。

但首先我创建了一个简单的工作来学习如何在 oracle 中创建工作，因为这是我第一次使用工作。

它以 systimestamp 运行，但作业不执行。

create or replace procedure job_test
is
begin
update table_a set value_user = 'JOB_EXECUTED' where id = 1; 
commit;
end;
/

然后调度器作业

begin 
dbms_scheduler.create_job(
job_name => 'test_job_A',
job_type => 'stored_procedure',
job_action => 'job_test',
start_date = SYSTIMESTAMP,
enabled => true
);
end;
/

然后我查阅了 value_user 列，它还没有更新。

 select * from table_A where id = 1;

谁能解释我错过了什么。

标签： oraclestored-proceduresdbms-scheduler

解决方案

create table user_count (
number_of_users NUMBER(4),
time_of_day   TIMESTAMP
);

CREATE OR REPLACE PROCEDURE insert_user_count AS
 v_user_count NUMBER(4);
BEGIN
 SELECT count(*)
  INTO v_user_count
 FROM v$session
 WHERE username IS NOT NULL;
 INSERT INTO user_count
 VALUES (v_user_count, systimestamp);
 commit;
 END insert_user_count;

BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
 program_name      => 'PROG_INSERT_USER_COUNT',
 program_action     => 'INSERT_USER_COUNT',
 program_type      => 'STORED_PROCEDURE');
END; 

BEGIN
 DBMS_SCHEDULER.CREATE_SCHEDULE (
 schedule_name   => 'my_weekend_5min_schedule',
 start_date    => SYSTIMESTAMP,
 repeat_interval  => 'FREQ=MINUTELY; INTERVAL=5; BYDAY=SAT,SUN',
 end_date     => SYSTIMESTAMP + INTERVAL '30' day,
 comments     => 'Every 5 minutes');
END;

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
  job_name     => 'my_user_count_job',
  program_name   => 'prog_insert_user_count',
  schedule_name   => 'my_weekend_5min_schedule');
END;

exec dbms_scheduler.enable('my_user_count_job’)

select * from user_count;

select job_name, status, run_duration, cpu_used
from USER_SCHEDULER_JOB_RUN_DETAILS
where job_name = ‘MY_USER_COUNT_JOB’;

推荐阅读