How to allow my DBA to pause and resume a stored procedure that updates every row in a large table?

Question

I have a table of about a million rows, and I need to update each row of the table with the result of a long calculation (the calculation gets a potentially excellent result for each row). Since this is time consuming, the database administrator must be able to control the execution. This specific calculation needs to be run once a year (it is a summary at the end of the year). I wanted to create a job using DBMS_SCHEDULER.CREATE_JOB, which would capture 100 rows from a table, update them, and then stop them; The next task execution then takes away the place where the previous execution was terminated.

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 while executing the stored procedure:

 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 would prefer to use DBMS_SCHEDULER, and I would prefer not to create any tables; why am I passing last_id to a stored procedure.

Answer 1

I would be wary of using such tasks to control progress. Either the delay between consecutive jobs will be too small for the database administrator to determine what work needs to be killed / paused / etc., or the delay will be long enough so that a significant part of the execution time is spent on delays between consecutive jobs.

Without creating any new objects, you can use the DBMS_ALERT package so that your database administrator can send a warning that pauses. Your code can call the DBMS_ALERT.WAITONE method every hundreds of lines to check if the database administrator has signaled a specific warning (i.e. the PAUSE_YEAREND_JOB warning). If no notification is received, the code may continue. If a warning is received, you can pause the code until another warning is received (i.e. RESUME_YEAREND_JOB ) or a fixed period of time or based on a message sent by the database administrator with the PAUSE_YEAREND_JOB notification (that is, the message may be the number of seconds for pause or date to pause before, etc.)

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

Answer 2

Another way to learn is with the dbms scheduler support tools for run windows and resource plans.

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

and:

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

Using windows and resource plans, your database administrator can simply configure the system to execute your procedure to follow certain rules — including the job window and execution using only a certain amount of resources (for example, CPU usage).

Thus, the procedure can be performed once a year, and CPU control can be monitored.

This can lead to manual control of your DBA.

Another idea is to write your own procedure to process all the records, but do it every 1000 or so. The dbms job.cancel () command can be used by your database administrator to cancel the job if they want to stop it, and then they can resume it (by reconfiguring or restarting) when they are ready to go. The trick will be that the procedure should be able to track processed lines, for example. using the processed_date column or a separate table listing the primary keys and the processed date.

Answer 3

In addition to DBMS_ALERT to DBMS_ALERT , your database administrator will appreciate being able to see where your stored procedure is located. You must use DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS in Oracle to do this.