oracle - 如何让我的 DBA 暂停并恢复更新大表中每一行的存储过程？

Question

我有一个大约一百万行的表，我需要用冗长的计算结果更新表中的每一行(计算得到的每一行的结果可能不同)。因为它很耗时，所以 DBA 必须能够控制执行。这个特定的计算需要每年运行一次(它做一个年终总结)。我想使用 DBMS_SCHEDULER.CREATE_JOB 创建一个作业，从表中抓取 100 行，更新它们然后停止；作业的下一次执行将在前一次执行停止的地方继续执行。

我的第一个想法是在存储过程的末尾包含此代码:

-- update 100 rows, storing the primary key of the last
-- updated row in last_id
-- make a new job that will run in about a minute and will
-- start from the primary key value just after last_id
dbms_scheduler.create_job
( job_name=>'yearly_summary'
, job_type=>'STORED_PROCEDURE'
, job_action=>'yearly_summary_proc(' || last_id || ')'
, start_date=>CURRENT_TIMESTAMP + 1/24/60
, enabled=>TRUE
);

但是当存储过程运行时我收到这个错误:

ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 99
ORA-06512: at "SYS.DBMS_SCHEDULER", line 262
ORA-06512: at "JBUI.YEARLY_SUMMARY_PROC", line 37
ORA-06512: at line 1

欢迎提出其他方法的建议。我更喜欢使用 DBMS_SCHEDULER 并且我不想创建任何表；这就是我将 last_id 传递给存储过程的原因。

Answer 1

我倾向于对使用这样的作业来控制执行持谨慎态度。要么连续作业之间的延迟往往太短，DBA 无法确定要终止/暂停/等的作业，要么延迟足够长，以至于运行时间的很大一部分将花费在连续作业之间的延迟上.

无需创建任何新对象，您可以使用 DBMS_ALERT包以允许您的 DBA 发送暂停作业的警报。您的代码可以调用 DBMS_ALERT.WAITONE方法每百行检查 DBA 是否已发出特定警报(即 PAUSE_YEAREND_JOB 警报)。如果没有收到警报，代码可以继续。如果收到警报，您可以暂停代码，直到收到另一个警报(即 RESUME_YEAREND_JOB )或一段固定的时间，或者基于 DBA 发送的消息 PAUSE_YEAREND_JOB警报(即消息可以是暂停的秒数或暂停的日期等)

当然，您可以通过创建一个新表来做同样的事情，让 DBA 向表中写入一行以暂停作业，然后每 N 行从表中读取一次。