One, start the process that executes the jobIn Oracle, it is a background process that uses the job queue of the job queue coordination process (CJQ0), which coordinates the database instance, to monitor the job table (job$) in the job queue and to start the job queue process (JNNN). The job queue jnnn will execute the job request created by the Dbms_jobs package when the job needs to be executed, as in the following procedure, which is extracted from the Dbms_jobs package.
* Process statement for the job request submitted:
PROCEDURE 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,
Instance in Binary_integer DEFAULT 0,
Force in BOOLEAN DEFAULT FALSE);
--Submit a new job. Chooses JOB from the sequence sys.jobseq.
--instance and force is added for JOBQ queue affinity
--If force is TRUE and then any positive an integer is acceptable as the job
--instance. If Force is FALSE and then the specified instance must be running;
--Otherwise the routine raises an exception.
--for example,
--variable x number;
--Execute Dbms_job.submit (: X, ' Pack.proc (' arg1 '); ', Sysdate, ' sysdate+1 ');
Following the meaning of the parameters given in the package, we can know what each parameter means when we need to add a job:
-job Specifies the activity number of the job being executed
-what specifies the PL/SQL code that needs to be executed
-Next_date job Next Run time, default value is Sysdate
-Interval here is the date function used to calculate the next job execution time, which is NULL by default. (Basic estimation)
-No_parse a tag. False (Default): Oracle parses the process associated with the job. True: Parses the process associated with the first executed job. For example, if you want to submit a job before creating the table associated with the job, you need to set it to true.
* Delete previously submitted jobs:
PROCEDURE remove (job in Binary_integer);
--Remove an existing job from the job queue.
--This currently does not stop a running job.
--Execute Dbms_job.remove (14144);
* Change Job:
PROCEDURE change (Job in Binary_integer,
What in VARCHAR2,
Next_date in Date,
Interval in VARCHAR2,
Instance in Binary_integer DEFAULT NULL,
Force in BOOLEAN DEFAULT FALSE);
--Change any of the the the User-settable fields in a job
--Parameter instance and force is added for job queue affinity
-If What, next_date,or interval is null, leave that value as-is.
--Instance defaults to NULL indicates instance affinity are not changed.
--If force was FALSE, the specified instance (to which the instance number
--change) must is running. Otherwise the routine raises an exception.
--If is TRUE, any positive an integer is acceptable as the job instance.
--Execute Dbms_job.change (14144, NULL, NULL, ' sysdate+3 ');
* Modify job description for the specified job
PROCEDURE what (Job in Binary_integer,
what in VARCHAR2);
-Existing job does, and replace its environment
* Modify the next execution time for the specified job
PROCEDURE next_date (Job in Binary_integer,
Next_date in date);
--Change if an existing job would next execute
* Modify the execution interval for the specified job
PROCEDURE interval (Job in Binary_integer,
Interval in VARCHAR2);
--Change how often a job executes* Set or reset the job corruption flag and the job cannot run if the job is corrupted
PROCEDURE broken (Job in Binary_integer,
Broken in BOOLEAN,
Next_date in date DEFAULT sysdate);
--Set the broken flag. Broken Jobs is never run.
* Force execution of a specified job
PROCEDURE run (Job in Binary_integer,
Force in BOOLEAN DEFAULT FALSE);
--Run job job now. Run It even if it is broken.
--Running The job would recompute Next_date, see View User_jobs.
--Execute Dbms_job.run (14144);
--Warning:this would reinitialize the current session ' s packages
--force was added for job queue affinity
--If Force is TRUE, instance affinity are irrelevant for running jobs in
-The foreground process. If Force is FALSE, the job can being run in the
--foreground only in the specified instance. Dbms_job.run would raise an
--exception if force are FALSE and the connected instance is the wrong one.
Where the initialization parameter job_queue_processes controls whether the DB instance can start a reconcile Job queue program process. If the value of this parameter is 0, it means that the job queue reconciliation process is not started when the database is started, and of course no jobs in the job queue are executed. This parameter can also specify the maximum number of jnnn processes that can run concurrently on an instance. The maximum number of processes that can be specified is 1000.
And the parameter is dynamic, you can change the settings dynamically using the following statement:
alter system set job_queue_processes = 20;
two. Manage Job queues1, to use and manage jobs in the job queue, you can use the procedures in the Dbms_job package, and you do not need database permissions to use the job queue, as long as you have permission to execute the job queue procedure.
2, let's give some examples, so that more "affordable":)
Declare
--Local variables here
Jobno number;
Begin
Dbms_job.submit (jobno, ' dbms_ddl.analyze_object ' (' TABLE ', ' SCOTT ', ' EMP ', ' ESTIMATE ', ' NULL ', '); ', sysdate , ' sysdate+1 ');
End
3, here are some of the properties related to the job (jobs):
* Job owner
* Job numberThe jobs in the queue are identified by the job number. When the job is submitted, its job number is automatically generated from the sys.jobseq sequence. Once assigned, the job number can no longer be changed.
* Job Definitionis the PL/SQL code that you specify when submitting the job (submit). It is important to note that in the job definition, you need to enclose the string in two single quotes, as if you were using a string in dynamic SQL, and you cannot run another job from one job.
* Job Execution intervalUsed to specify the interval between two job executions, or how often your job will be executed at intervals, especially for periodic tasks.
Some of the commonly used date expressions:
-Sysdate+7 Run once a week
-SYSDATE+1/48 run every half hour
-Next_day (Trunc (sysdate), "MONDAY") +15/24 runs every Monday 3 o'clock in the afternoon
-Next_day (Add_months (Trunc (sysdate, "Q"), 3), ' Trusday ') the first Thursday of each quarter
Attention:
(1) For example, if you set the execution interval to "sysdate+7" in Monday, but for some reason, it is not executed until Thursday, the next execution time is to start executing the job every Thursday. If the value of the interval date function is NULL,
(2) Of course, if you want your job to start executing at some point in time, regardless of the end time of the last execution, your interval and next_date parameters should be specified in the following way: Next_day (Trunc (sysdate), "MONDAY")
4, let's look at how the job queue is running, and the view of its running state
(1) Oracle uses job queue locks to ensure that each job is run only in one session. You can use the lock view to view information about the current lock for the session program:
SELECT * from v$lock where type = ' JQ ';
You can also view the status of a running job by looking at:
SELECT * from Dba_jobs_running;
(2) Job execution error, information about the failure is recorded in the trace file and the alert log, and Oracle writes a message numbered Ora-12012 and includes the job number of the failed job.
(3) Delete the job, dbms_job.remove (job number);
(4) Change the job,
-Dbms_job.change (Operation number, null,null, ' sysdate+3 ');
Change job to run every three days
-Dbms_job.what (Operation number, ' Scott.emppackage.give_raise (' abc ', 6000.00);
Change the definition of a job
-Dbms_job.next_date (Operation number, ' sysdate+5 ');
Change the next execution time for a job
-Dbms_job.interval (operation number, NULL);
The job will no longer run after the job is successfully executed
third, view job queue informationThe associated data dictionary views are:
* All jobs in the Dba_jobs database
* All_jobs All jobs that the current user can access
* User_jobs All jobs belonging to the current user
* dba_jobs_running List all jobs currently running in the database
Job queue and queue scheduling in Oracle
