|Oracle7 Server Administrator's Guide||
To maximize performance and accommodate many users, a multi-process Oracle7 system uses some additional processes called background processes. Background processes consolidate functions that would otherwise be handled by multiple Oracle programs running for each user process. Background processes asynchronously perform I/O and monitor other Oracle processes to provide increased parallelism for better performance and reliability.
SNP background processes execute job queues. SNP processes periodically wake up and execute any queued jobs that are due to be run. You must have at least one SNP process running to execute your queued jobs in the background.
SNP background processes differ from other Oracle7 background processes, in that the failure of an SNP process does not cause the instance to fail. If an SNP process fails, Oracle7 restarts it.
See Also: For more information on background processes, see Oracle7 Server Concepts.
Table 7 - 1 describes the job queue initialization parameters.
|Range of values:||0...10|
|Multiple instances:||can have different values|
|Sets the number of SNP background processes per instance.|
|Range of values:||1...3600 (seconds)|
|Multiple instances:||can have different values|
|Sets the interval between wake-ups for the SNP background processes of the instance.|
|SUBMIT||Submits a job to the job queue.||page 7 - 4|
|REMOVE||Removes specified job from the job queue.||page 7 - 9|
|CHANGE||Alters a specified job. You can alter the job description, the time at which the job will be run, or the interval between executions of the job.||page 7 - 10|
|WHAT||Alters the job description for a specified job.||page 7 - 10|
|NEXT_DATE||Alters the next execution time for a specified job.||page 7 - 11|
|INTERVAL||Alters the interval between executions for a specified job.||page 7 - 11|
|BROKEN||Disables job execution. If a job is marked as broken, Oracle7 does not attempt to execute it.||page 7 - 11|
|RUN||Forces a specified job to run.||page 7 - 12|
DBMS_JOB.SUBMIT( job OUT BINARY_INTEGER, what IN VARCHAR2, next_date IN DATE DEFAULT SYSDATE, interval IN VARCHAR2 DEFAULT 'null', no_parse IN BOOLEAN DEFAULT FALSE)
The SUBMIT procedure returns the number of the job you submitted. Table 7 - 3 describes the procedure's parameters.
|job||This is the identifier assigned to the job you created. You must use the job number whenever you want to alter or remove the job.|
|For more information about job numbers, see "Job Numbers" .|
|what||This is the PL/SQL code you want to have executed.|
|For more information about defining a job, see "Job Definitions" .|
|next_date||This is the next date when the job will be run. The default value is SYSDATE.|
|interval||This is the date function that calculates the next time to execute the job. The default value is NULL. INTERVAL must evaluate to a future point in time or NULL.|
|For more information on how to specify an execution interval, see page 7 - 7.|
|no_parse||This is a flag. The default value is FALSE.|
|If NO_PARSE is set to FALSE (the default), Oracle7 parses the procedure associated with the job. If NO_PARSE is set to TRUE, Oracle7 parses the procedure associated with the job the first time that the job is executed. If, for example, you want to submit a job before you have created the tables associated with the job, set NO_PARSE to TRUE.|
SVRMGR> VARIABLE jobno number;
SVRMGR> begin 2> DBMS_JOB.SUBMIT(:jobno, 3> 'dbms_ddl.analyze_object(''TABLE'', 4> ''DQUON'', ''ACCOUNTS'', 5> ''ESTIMATE'', NULL, 50);' 6> SYSDATE, 'SYSDATE + 1'); 7> end; 8> / Statement processed.
SVRMGR> print jobno JOBNO ---------- 14144
You can change a job's environment by using the DBMS_SQL package and the ALTER SESSION command.
Note: If the job number of a job you want to import matches the number of a job already existing in the database, you will not be allowed to import that job. Submit the job as a new job in the database.
Once a job is assigned a job number, that number does not change. Even if the job is exported and imported, its job number remains the same.
Normally the job definition is a single call to a procedure. The procedure call can have any number of parameters.
Note: In the job definition, use two single quotation marks around strings. Always include a semicolon at the end of the job definition.
There are special parameter values that Oracle7 recognizes in a job definition. Table 7 - 4 lists these parameters.
|job||IN||The number of the current job.|
|next_date||IN/OUT||The date of the next execution of the job. The default value is SYSDATE.|
|broken||IN/OUT||Status of job, broken or not broken. The IN value is FALSE.|
'myproc(''10-JAN-82'', next_date, broken);'
If a job should be executed periodically at a set interval, use a date expression similar to 'SYSDATE + 7' in the INTERVAL parameter. For example, if you set the execution interval to 'SYSDATE + 7' on Monday, but for some reason (such as a network failure) the job is not executed until Thursday, 'SYSDATE + 7' then executes every Thursday, not Monday.
If you always want to automatically execute a job at a specific time, regardless of the last execution (for example, every Monday), the INTERVAL and NEXT_DATE parameters should specify a date expression similar to 'NEXT_DAY(TRUNC(SYSDATE), ''MONDAY'')'.
Table 7 - 5 lists some common date expressions used for job execution intervals.
|'SYSDATE + 7'||exactly seven days from the last execution|
|'SYSDATE + 1/48'||every half hour|
|'NEXT_DAY(TRUNC(SYSDATE), ''MONDAY'') + 15/24'||every Monday at 3PM|
|'NEXT_DAY(ADD_MONTHS (TRUNC(SYSDATE, ''Q''), 3), ''THURSDAY'')'||first Thursday of each quarter|
See Also: For more information about the ALTER SESSION command, see Oracle7 Server SQL Reference.
For more information on the DBMS_SQL package, see the Oracle7 Server Application Developer's Guide.
When an SNP process runs a job, the job is run in the same environment in which it was submitted and with the owner's default privileges.
When you force a job to run using the procedure DBMS_JOB.RUN, the job is run by your user process. When your user process runs a job, it is run with your default privileges only. Privileges granted to you through roles are unavailable.
Interpreting Information about JQ Locks You can use the Server Manager Lock Monitor or the locking views in the data dictionary to examine information about locks currently held by sessions. The following query lists the session identifier, lock type, and lock identifiers for all sessions holding JQ locks:
SVRMGR> SELECT sid, type, id1, id2 2> FROM v$lock 3> WHERE type = 'JQ';
SID TY ID1 ID2 ---------- -- ---------- ---------- 12 JQ 0 14144 1 row selected.
In the query above, the identifier for the session holding the lock is 12. The ID1 lock identifier is always 0 for JQ locks. The ID2 lock identifier is the job number of the job the session is running.
The following can prevent the successful execution of queued jobs:
See Also: For more information about the locking views, see the .
For more information about locking, see .
DBMS_JOB.REMOVE(job IN BINARY_INTEGER)
The following statement removes job number 14144 from the job queue:
You can only remove jobs you own. If you try to remove a job that you do not own, you receive a message that states the job is not in the job queue.
Here's an example where the job identified as 14144 is now executed every three days:
DBMS_JOB.CHANGE(14144, null, null, 'SYSDATE + 3');
DBMS_JOB.CHANGE( job IN BINARY_INTEGER, what IN VARCHAR2, next_date IN DATE, interval IN VARCHAR2)
If you specify NULL for WHAT, NEXT_DATE, or INTERVAL when you call the procedure CHANGE, the current value remains unchanged.
Note: When you change a job's definition using the WHAT parameter in the procedure CHANGE, Oracle7 records your current environment. This becomes the new environment for the job.
DBMS_JOB.WHAT( job IN BINARY_INTEGER, what IN VARCHAR2)
Note: When you execute procedure WHAT, Oracle7 records your current environment. This becomes the new environment for the job.
DBMS_JOB.NEXT_DATE( job IN BINARY_INTEGER, next_date IN DATE)
DBMS_JOB.INTERVAL( job IN BINARY_INTEGER, interval IN VARCHAR2)
When you submit a job it is considered not broken.
There are two ways a job can break:
DBMS_JOB.BROKEN( job IN BINARY_INTEGER, broken IN BOOLEAN, next_date IN DATE DEFAULT SYSDATE)
The following example marks job 14144 as not broken and sets its next execution date to the following Monday:
DBMS_JOB.BROKEN(14144, FALSE, NEXT_DAY(SYSDATE, 'MONDAY'));
Once a job has been marked as broken, Oracle7 will not attempt to execute the job until you either mark the job as not broken, or force the job to be executed by calling the procedure DBMS_JOB.RUN.
Once you reset a job's broken flag (by calling either RUN or BROKEN), job execution resumes according to the scheduled execution intervals set for the job.
To force a job to be executed immediately, use the procedure RUN in the DBMS_JOB package. Oracle7 attempts to run the job, even if the job is marked as broken:
DBMS_JOB.RUN( job IN BINARY_INTEGER)
When you run a job using DBMS_JOB.RUN, Oracle7 recomputes the next execution date. For example, if you create a job on a Monday with a NEXT_DATE value of 'SYSDATE' and an INTERVAL value of 'SYSDATE + 7', the job is run every 7 days starting on Monday. However, if you execute RUN on Wednesday, the next execution date will be the next Wednesday.
Note: When you force a job to run, the job is executed in your current session. Running the job reinitializes your session's packages.
The following statement runs job 14144 in your session and recomputes the next execution date:
The procedure RUN contains an implicit commit. Once you execute a job using RUN, you cannot rollback.
After you have identified the session running the job (via V$SESSION), you can disconnect the session using the Server Manager Disconnect Session menu item, or the SQL command ALTER SYSTEM.
See Also: For examples of viewing information about jobs and sessions, see "Viewing Job Queue Information" .
For more information on V$SESSION, see the Oracle7 Server Reference.
|DBA_JOBS||Lists all the jobs in the database.|
|USER_JOBS||Lists all jobs owned by the user.|
|DBA_JOBS_RUNNING||Lists all jobs in the database that are currently running. This view joins V$LOCK and JOB$.|
For example, you can display information about a job's status and failed executions. The following sample query creates a listing of the job number, next execution time, failures, and broken status for each job you have submitted:
SVRMGR> SELECT job, next_date, next_sec, failures, broken 2> FROM user_jobs;
JOB NEXT_DATE NEXT_SEC FAILURES B ---------- --------- -------- ---------- - 9125 01-NOV-94 00:00:00 4 N 14144 24-OCT-94 16:35:35 0 N 41762 01-JAN-00 00:00:00 16 Y 3 rows selected.
You can also display information about jobs currently running. The following sample query lists the session identifier, job number, user who submitted the job, and the start times for all currently running jobs:
SVRMGR> SELECT sid, r.job, log_user, r.this_date, r.this_sec 2> FROM dba_jobs_running r, dba_jobs j 3> WHERE r.job = j.job;
SID JOB LOG_USER THIS_DATE THIS_SEC ---------- ---------- -------------------- --------- -------- 12 14144 JFEE 24-OCT-94 17:21:24 25 8536 SCOTT 24-OCT-94 16:45:12 2 rows selected.
See Also: For more information on data dictionary views, see the Oracle7 Server Reference.
Copyright © 1996 Oracle Corporation.
All Rights Reserved.