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

I have a table of about a million rows and I need to update every row in the table with the result of a lengthy calculation (the calculation gets a potentially different result for each row). Because it is time consuming, the DBA must be able to control execution. This particular calculation needs to be run once a year (it does a year-end summary). I wanted to create a job using DBMS_SCHEDULER.CREATE_JOB that would grab 100 rows from the table, update them and then stop; the next execution of the job would then pick up where the prior execution left off.

我有一个大约一百万行的表,我需要用一个冗长的计算结果更新表中的每一行(计算得到每行的可能不同的结果)。因为它很耗时,所以DBA必须能够控制执行。这个特殊的计算需要每年运行一次(它是年终摘要)。我想使用DBMS_SCHEDULER.CREATE_JOB创建一个作业,它可以从表中获取100行,更新它们然后停止;然后,下一次执行该作业将获取先前执行中断的位置。

My first thought was to include this code at the end of my stored procedure:

我的第一个想法是在我的存储过程结束时包含此代码:

-- 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
);

But I get this error when the stored procedure runs:

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

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

Suggestions for other ways to do this are welcome. I'd prefer to use DBMS_SCHEDULER and I'd prefer not to have to create any tables; that's why I'm passing in the last_id to the stored procedure.

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

3 个解决方案

#1

I would tend to be wary about using jobs like this to control execution. Either the delay between successive jobs would tend to be too short for the DBA to figure out what job to kill/ pause/ etc. or the delay would be long enough that a significant fraction of the run time would be spent in delays between successive jobs.

我倾向于谨慎使用这样的工作来控制执行。连续作业之间的延迟往往太短,以至于DBA无法确定要杀死/暂停/等等的工作,或者延迟时间足够长,以至于大部分运行时间将用于连续作业之间的延迟。

Without creating any new objects, you can use the DBMS_ALERT package to allow your DBA to send an alert that pauses the job. Your code could call the DBMS_ALERT.WAITONE method every hundred rows to check whether the DBA has signaled a particular alert (i.e. the PAUSE_YEAREND_JOB alert). If no alert was received, the code could continue on. If an alert was received, you could pause the code either until another alert (i.e. RESUME_YEAREND_JOB) was received or a fixed period of time or based on the message the DBA sent with the PAUSE_YEAREND_JOB alert (i.e. the message could be a number of seconds to pause or a date to pause until, etc.)

在不创建任何新对象的情况下,您可以使用DBMS_ALERT包来允许DBA发送暂停作业的警报。您的代码可以每百行调用DBMS_ALERT.WAITONE方法来检查DBA是否已发出特定警报(即PAUSE_YEAREND_JOB警报)。如果未收到任何警报,则代码可以继续。如果收到警报,您可以暂停代码,直到收到另一个警报(即RESUME_YEAREND_JOB)或一段固定的时间,或者根据DBA使用PAUSE_YEAREND_JOB警报发送的消息(即消息可能是几秒钟暂停或暂停的日期,等等)

Of course, you could do the same thing by creating a new table, having the DBA write a row to the table to pause the job, and reading from the table every N rows.

当然,您可以通过创建新表来执行相同的操作,让DBA向表中写入一行以暂停作业,并从表中读取每N行。

#2

Another avenue to explore would be the dbms scheduler's support tools for execution windows and resource plans.

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

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

and also:

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

With windows and resource plans your DBA can simply configure the system to execute your procedure to obey certain rules - including a job window and executing using only a certain number of resources (i.e. CPU usage).

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

This way the procedure can run once a year, and CPU usage can be controlled.

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

This though may not provide the manual control your DBA would like.

这虽然可能无法提供您的DBA所需的手动控制。

Another idea would be to write your procedure to process all records, but commit every 1000 or so. The dbms job.cancel() command could be used by your DBA to cancel the job if they wanted it to stop, and then they can resume it (by rescheduling or rerunning it) when they're ready to go. The trick would be that the procedure would need to be able to keep track of rows processed, e.g. using a 'processed_date' column, or a separate table listing primary keys and processed date.

另一个想法是编写您的过程来处理所有记录,但每1000左右提交一次。 DBA可以使用dbms job.cancel()命令取消作业,如果他们希望它停止,然后他们可以在准备就绪时恢复它(通过重新安排或重新运行)。诀窍是程序需要能够跟踪处理的行,例如使用'processed_date'列或列出主键和处理日期的单独表。

#3

In addition to the answer about DBMS_ALERT, your DBA would appreciate the ability to see where your stored procedure is up to. You should use the DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS functionality in Oracle to do this.

除了关于DBMS_ALERT的答案之外,您的DBA还希望能够查看存储过程的位置。您应该使用Oracle中的DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS功能来执行此操作。

#1