Wednesday, April 15, 2009

Oracle DBMS_JOB

Oracle Parameters Which Controls the Jobs:

SNP Background Processes:

SNP processes run in the background and implement database snapshots and job queues. If an SNP process fails, Oracle restarts it without affecting the rest of the database. An SNP process can run one job at a time with a maximum of ten SNP processes running simultaneously. The INIT.ORA initialization file contains three parameters that control the behavior of the SNP processes:
Parameters
JOB_QUEUE_PROCESSES:
How many processes to start. If set to zero, no jobs are executed. Default is 0. Range is 0 to 1000.
JOB_QUEUE_INTERVAL (obsolete in 9i):
How long an interval the process will sleep before checking for a new job. Default is 60 sec. Range is 1 to 3600 sec
JOB_QUEUE_KEEP_CONNECTIONS:
Controls whether an SNP process closes any remote database connections. Default is False. Range is True/False.

DBMS_JOB:
The DBMS_JOB package schedules and manages jobs in the job queue.
Note: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.

This chapter contains the following topics:
Using DBMS_JOB
--Security Model
--Operational Notes
Summary of DBMS_JOB Subprograms

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: Once a job is started and running, there is no easy way to stop the job.

Note: You must issue a COMMIT statement immediately after executing the statement of dbms_job subprograms.

Click on the links to go through the syntax and format of DBMS_JOB.

I would like to suggest to refer "All about Database Jobs and Intervals" document for more information and better understanding:

http://www.pafumi.net/dbms_job.htm

Also you can refer Scheduling Jobs Using Oracle’s Job Queue for more information about Oracle scheduled jobs.

No comments:

Post a Comment