RelationalDBDesign RelationalDBDesign


Parallel Server   «Prev 

Using DBMS_JOB to control job execution

The DBMS_JOB package has been superseded by the DBMS_SCHEDULER package. In particular, if you are administering jobs to manage system load, you should consider disabling DBMS_JOB by revoking the package execution privilege for users.
The DBMS_JOB package schedules and manages jobs in the job queue.

Security Model

No specific system privileges are required to use DBMS_JOB. No system privileges are available to manage DBMS_JOB. Jobs cannot be altered or deleted other than jobs owned by the user. This is true for all users including those users granted DBA privileges.
You can execute procedures that are owned by the user or for which the user is explicitly granted EXECUTE. However, procedures for which the user is granted the execute privilege through roles cannot be executed.
Note that, once a job is started and running, there is no easy way to stop the job.

using DBMS_JOB to control job execution
1) Here is an example of submitting a job using DBMS_JOB. Notice the parameter called instance.
This contains the number of the instance in the OPS that is allowed to run this job.

using DBMS_JOB to control job execution
2) The USER_JOBS data dictionary view stores information about jobs in the job queue. Here is a query that shows the job that we created.

using DBMS_JOB to control job execution
3) To remove a job from the job queue, use the DBMS_JOB.REMOVE procedure. Here, we are removing the job we created earlier.