How do I let my DBA pause and resume a stored procedure that is updating every row in a large table?

Question

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.

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.

Answer 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.

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

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.

Answer 2

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

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

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

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

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.

Answer 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.