Oracle inside (9)

A job is a storage program scheduled to run at a specific time or after a specific event.
9.1 dbms_job package
The dbms_job package submits a job to the job queue. Job Queue is the place where jobs are stored. You can arrange this through Job Queue
You can also find information about the currently running jobs, terminated queues, Job Scheduling, or other
Job information.
Process Name Description
Broken disables job running. If the code is marked as corrupted, Oracle will not execute it.
Change modify detailed information of a specified job, such as job description, job running time, or job execution Interval
Interval modifies the execution interval of a specified job.
Next_date: modifies the next execution time of a specified job.
Remove deletes a specific job from the queue
Run enforces a specific job
Submit submits a job to the Job Queue
What modify the job description of a specific job
9.2 use background processes
Oracle does not run the same program for each user to schedule or run jobs, which wastes a lot of resources. Instead, it provides SNP (snapshot
Refresh process) background processes. These background processes share the same functions and code, which enables them to monitor possible concurrent Oracle processes.
A key feature of SNP is that a job fails without degrading the database performance. Moreover, the SNP process monitors jobs at user-specified intervals,
Advanced PL/SQL programming
35th pages 35 pages
Start all processes to be executed and wait for the next interval.
Oracle can provide up to 10 SNP processes, which are identified as snp0 to snp9. Some parameters are defined in the init. ora file.
SNP Parameters
Parameter value range default value description
Job_queue_process 0-10 0 determines the number of background processes started for each instance
Job_queue_interval 1-3600 60 time interval between the SNP searches for jobs to be executed (
Seconds)
Job_queue_keep_connections true/False false
If true, all
The database connection remains open; otherwise, as needed
To enable or disable a connection
Note: Each job uses one process, so there cannot be jobs executed across multiple processes. If job_queue_process is
If it is set to 0 (default value), the job cannot be executed. You must modify this parameter in init. ora.
9.3 run the job
There are two ways to execute a job:
1) Submit the job to the Job Queue for scheduled execution;
2) execute it immediately.
9.3.1 use submit to submit a job to the Job Queue
Procedure submit (
Job_name out binary_integer,
Job_to_submit in varchar2,
Next_run in date default sysdate,
Interval in varcahr2 default null,
Job_parsing in Boolean default false,
Instance in binary_integer default any_instance,
Force in Boolean default false );