重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛

标题: oracle定时任务常用攻略 [打印本页]

作者: mahan 时间: 2025-3-28 23:10
标题: oracle定时任务常用攻略
在 Oracle 19c 中，若想创建个定时任务，可以通过 DBMS_SCHEDULER 或 DBMS_JOB 来创建定时任务，确保存储过程 pro_test 每天零点自动执行一次。

1. 创建作业
1.1.授权说明
-- 授权创建JOB权限
GRANT CREATE JOB TO <USER>

-- 若要存储过程属于其他用户，需要授权
GRANT EXECUTE ON PRO_TEST TO <USER>;
1.2.创建作业（Job）
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
job_name       => 'JOB_PRO_TEST_DAILY',  -- 作业名称
job_type       => 'STORED_PROCEDURE', -- 作业类型（存储过程）
job_action    => 'PRO_TEST',          -- 存储过程名
start_date    => SYSTIMESTAMP,       -- 开始时间（立即生效）
repeat_interval => 'FREQ=DAILY; BYHOUR=0; BYMINUTE=0; BYSECOND=0', -- 每天零点执行
enabled       => TRUE,                -- 启用作业
comments       => 'Daily execution of PRO_TEST at midnight'
  );
END;
/
关键参数说明

repeat_interval：
FREQ=DAILY：每天执行。
BYHOUR=0：在 0 点执行。
BYMINUTE=0 和 BYSECOND=0：精确到 00:00:00。
1.3.手动执行进行测试
BEGIN
  DBMS_SCHEDULER.RUN_JOB('JOB_PRO_TEST_DAILY');
END;
/
2.查看 DBMS_SCHEDULER 作业的详细信息
2.1 查询 *_SCHEDULER_JOBS 视图
-- 查看当前用户下的作业
SELECT job_name, job_type, job_action, start_date, repeat_interval, enabled, state
FROM USER_SCHEDULER_JOBS;

-- 查看所有作业（需 DBA 权限）
SELECT job_name, owner, job_type, job_action, start_date, repeat_interval, enabled, state
FROM DBA_SCHEDULER_JOBS;
2.2 查询 *_SCHEDULER_PROGRAMS（如果作业关联了程序）
SELECT program_name, program_type, program_action
FROM USER_SCHEDULER_PROGRAMS;
2.3 查询 *_SCHEDULER_JOB_ARGS（查看作业参数）
SELECT job_name, argument_name, argument_value
FROM USER_SCHEDULER_JOB_ARGS
WHERE job_name = 'JOB_PRO_TEST_DAILY';
2.4 使用 DBMS_SCHEDULER.GET_ATTRIBUTE 获取作业属性
DECLARE
  v_repeat_interval VARCHAR2(1000);
BEGIN
  DBMS_SCHEDULER.GET_ATTRIBUTE(
name    => 'JOB_PRO_TEST_DAILY',
attribute => 'repeat_interval',
value    => v_repeat_interval
  );
  DBMS_OUTPUT.PUT_LINE('Repeat Interval: ' || v_repeat_interval);
END;
/
3.查看作业运行状况
-- 查看作业运行历史
SELECT job_name, status, log_date, run_duration
FROM USER_SCHEDULER_JOB_RUN_DETAILS
WHERE job_name = 'JOB_PRO_TEST_DAILY';

-- 查看作业错误日志
SELECT job_name, status, error#, error_msg
FROM USER_SCHEDULER_JOB_RUN_DETAILS
WHERE status = 'FAILED';
4. 修改 DBMS_SCHEDULER 作业（如需）
4.1 使用 DBMS_SCHEDULER.SET_ATTRIBUTE 修改作业属性
-- 修改作业的执行时间（每天 1:00 执行）
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
name    => 'JOB_PRO_TEST_DAILY',
attribute => 'repeat_interval',
value    => 'FREQ=DAILY; BYHOUR=1; BYMINUTE=0; BYSECOND=0'
  );
END;
/

-- 修改作业的存储过程（job_action）
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
name    => 'JOB_PRO_TEST_DAILY',
attribute => 'job_action',
value    => 'pro_test_v2'  -- 新的存储过程名
  );
END;
/

-- 修改作业的启用状态
BEGIN
  DBMS_SCHEDULER.ENABLE('JOB_PRO_TEST_DAILY');  -- 启用
  DBMS_SCHEDULER.DISABLE('JOB_PRO_TEST_DAILY'); -- 禁用
END;
/
4.2 使用 DBMS_SCHEDULER.COPY_JOB 复制并修改作业
-- 复制作业并修改
BEGIN
  DBMS_SCHEDULER.COPY_JOB(
source_job    => 'JOB_PRO_TEST_DAILY',
destination_job => 'JOB_PRO_TEST_DAILY_V2'
  );
  DBMS_SCHEDULER.SET_ATTRIBUTE(
name    => 'JOB_PRO_TEST_DAILY_V2',
attribute => 'job_action',
value    => 'pro_test_v2'
  );
END;
/
4.3 删除并重新创建作业
-- 删除作业
BEGIN
  DBMS_SCHEDULER.DROP_JOB('JOB_PRO_TEST_DAILY');
END;
/

-- 重新创建作业
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
job_name       => 'JOB_PRO_TEST_DAILY',
job_type       => 'STORED_PROCEDURE',
job_action    => 'pro_test_v2',
start_date    => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY; BYHOUR=0; BYMINUTE=0; BYSECOND=0',
enabled       => TRUE
  );
END;
/
5.字典说明
操作  方法
查看作业定义  USER_SCHEDULER_JOBS、DBMS_SCHEDULER.GET_ATTRIBUTE
修改作业属性  DBMS_SCHEDULER.SET_ATTRIBUTE
修改作业逻辑  复制作业（COPY_JOB）或删除后重新创建
查看执行日志  USER_SCHEDULER_JOB_RUN_DETAILS

欢迎光临重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/)