如何让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包来允许数据库管理员发送暂停作业的警报。您的代码可以每隔100行调用一次DBMS_ALERT.WAITONE方法，以检查数据库管理员是否发出了特定的警报(即PAUSE_YEAREND_JOB警报)。如果没有收到警报，代码可以继续。如果收到警报，您可以暂停代码，直到收到另一个警报(即RESUME_YEAREND_JOB)，或者暂停一段固定的时间，或者根据数据库管理员发送的带有PAUSE_YEAREND_JOB警报的消息(即消息可以是暂停的秒数或暂停到的日期，等等)。

当然，您也可以这样做:创建一个新表，让DBA向表中写入一行以暂停作业，然后每隔N行从表中读取一次。

Answer 2

另一个可以探索的途径是dbms调度器对执行窗口和资源计划的支持工具。

http://www.oracle-base.com/articles/10g/Scheduler10g.php

还有：

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14231/schedover.htm#sthref3501

使用windows和资源计划，您的DBA可以简单地配置系统来执行您的过程，以遵守特定的规则-包括作业窗口和仅使用一定数量的资源(即CPU使用率)执行。

这样，该过程可以每年运行一次，并且可以控制CPU使用率。

但是，这可能无法提供您的DBA想要的手动控制。

另一个想法是编写处理所有记录的过程，但是每1000次左右提交一次。如果您的数据库管理员希望停止作业，那么可以使用dbms job.cancel()命令来取消作业，然后在准备就绪时可以恢复作业(通过重新调度或重新运行)。诀窍在于，该过程需要能够跟踪处理的行，例如，使用“processed_date”列，或列出主键和处理日期的单独表。

Answer 3

除了回答关于DBMS_ALERT的问题之外，您的数据库管理员还希望能够看到您的存储过程的进展情况。您应该使用Oracle中的DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS功能来完成此操作。