Shulou(Shulou.com)06/01 Report--

Oracle knowledge points-job

Job in oracle is similar to crontab in Linux and is used to perform certain operations on a regular basis.

Dependent view: dba_jobs,user_jobs,all_jobs,dba_jobs_running

Related parameters: job_queue_processes

Related package: dbms_job

Explanation of dba_jobs and dba_jobs_running fields:

Dba_jobs describes all the job in the database

Desc dba_jobs

About the value of the interval parameter

Dba_jobs_running lists all running job in the current instance

Desc dba_jobs_running

Explanation of job_queue_processes:

Specifies the maximum number of dbms_job jobs and Oracle Scheduler (dbms_scheduler) jobs in a single instance. Job jobs and Oracle Scheduler will not run on the instance if job_queue_processes is set to 0. 0. The value range is 0-1000.

Explanation of the dbms_job package:

Dbms_job includes broken (), change (), instance (), interval (), isubmit (), next_date (), remove (), run (), submit (), user_export (), what ().

The 1.broken () process updates the status of a submitted job to mark a broken job as unbroken work.

PROCEDURE BROKEN Argument Name Type In/Out Default?-JOB BINARY_INTEGER IN BROKEN BOOLEAN IN NEXT_DATE DATE IN DEFAULT

The job parameter is the work number, which uniquely identifies the work in the question.

The broken parameter indicates whether the work will be marked as broken-- TRUE indicates that the job will be marked as broken, and FLASE indicates that the work will be marked as unbroken.

The next_date parameter indicates when this work will run again. The default value for this parameter is the current date and time.

If job fails to make a successful trip for some reason, oracle will retry 16 times and fail to execute successfully. It will be marked as broken to restart job with the status of broken.

The 2.change () procedure is used to change the settings for a specified job.

PROCEDURE CHANGE Argument Name Type In/Out Default?-JOB BINARY_INTEGER IN WHAT VARCHAR2 IN NEXT_DATE DATE IN INTERVAL VARCHAR2 IN INSTANCE BINARY_INTEGER IN DEFAULT FORCE BOOLEAN IN DEFAULT

The job parameter is an integer value that uniquely identifies this work.

The What parameter is a block of PL/SQL code that runs from this job.

The next_date parameter indicates when the work will be performed.

The interval parameter indicates how often a job is reperformed.

3.instance () process

PROCEDURE INSTANCE Argument Name Type In/Out Default?-JOB BINARY_INTEGER IN INSTANCE BINARY_INTEGER IN FORCE BOOLEAN IN DEFAULT

The 4.interval () procedure is used to explicitly set the number of intervals between re-performing a job.

PROCEDURE INTERVAL Argument Name Type In/Out Default?-JOB BINARY_INTEGER IN INTERVAL VARCHAR2 IN

The interval parameter indicates how often a job is reperformed

The 5.isubmit () procedure is used to submit a job with a specific work number.

PROCEDURE ISUBMIT Argument Name Type In/Out Default?-JOB BINARY_INTEGER IN WHAT VARCHAR2 IN NEXT_DATE DATE IN INTERVAL VARCHAR2 IN DEFAULT NO_PARSE BOOLEAN IN DEFAULT

The only difference between this procedure and the submit () procedure is that this job parameter is passed as an in parameter and includes a work number provided by the developer. If the work number provided is already in use, an error will be generated

The 6.next_date () procedure is used to explicitly set the execution time of a job.

FUNCTION IS_JOBQ RETURNS BOOLEANPROCEDURE NEXT_DATE Argument Name Type In/Out Default?-JOB BINARY_INTEGER IN NEXT_DATE DATE IN

The next_date parameter indicates the date and time that this work should be performed

The 7.remove () procedure is used to delete a job that is scheduled to run

PROCEDURE REMOVE Argument Name Type In/Out Default?-JOB BINARY_INTEGER IN

The value of this parameter is the value of the job parameter returned by calling the submit () procedure for this work. Work that is already running cannot be deleted by the called program.

The 8.run () procedure is used to perform a specified work immediately.

PROCEDURE RUN Argument Name Type In/Out Default?-JOB BINARY_INTEGER IN FORCE BOOLEAN IN DEFAULT

The 9.submit () process work is planned normally.

PROCEDURE SUBMIT Argument Name Type In/Out Default?-JOB BINARY_INTEGER OUT WHAT VARCHAR2 IN NEXT_DATE DATE IN DEFAULT INTERVAL VARCHAR2 IN DEFAULT NO_PARSE BOOLEAN IN DEFAULT INSTANCE BINARY_INTEGER IN DEFAULT FORCE BOOLEAN IN DEFAULT

The job parameter is the binary_ineger returned by the submit () procedure. This value is used to uniquely identify a job.

The what parameter is the block of PL/SQL code that will be executed.

The next_date parameter indicates when the work will be run.

The interval parameter when the work will be re-performed.

The no_parse parameter indicates whether this work should be parsed on submission or execution-- TRUE indicates that this PL/SQL code should be parsed on its first execution, and FALSE indicates that this PL/SQL code should be parsed immediately.

The 10.user_export () procedure returns a command that arranges an existing job so that it can be resubmitted.

PROCEDURE USER_EXPORT Argument Name Type In/Out Default?-JOB BINARY _ INTEGER IN MYCALL VARCHAR2 IN/OUT

The my_call parameter contains the body needed to resubmit this work in its current state

11.user_export () process

PROCEDURE USER_EXPORT Argument Name Type In/Out Default?-JOB BINARY _ INTEGER IN MYCALL VARCHAR2 IN/OUT MYINST VARCHAR2 IN/OUT

12.what () process

PROCEDURE WHAT Argument Name Type In/Out Default?-JOB BINARY_INTEGER IN WHAT VARCHAR2 IN

The what parameter indicates the new PL/SQL code to be executed.

View the job information under the current user

SQL > show userUSER is "HR" SQL > SELECT SID, TYPE, ID1, ID2 FROM V$LOCK WHERE TYPE = 'JQ'; (view lock information in job) no rows selectedSQL > select job,what,failures,broken from user_jobs; (view current user job information) no rows selectedSQL > select sid, r.job, log_user, r.this_date, r.this_sec from dba_jobs_running r, dba_jobs j where r.job = j.job (view the job information currently running by the user) no rows selectedSQL > alter system set job_queue_processes = 20 (set the number of job queues) System altered.SQL > show parameter job_queue_processesNAME TYPE VALUE -- job_queue_processes integer 20

Job creation and testing

SQL > create table test_job (x date); Table created.SQL > create or replace procedure p_insert_to_test_job 2 as 3 begin 4 insert into test_job values (sysdate); 5 end; 6 / Procedure created.SQL > variable job_num number; SQL > begin 2 dbms_job.submit (: job_num,'p_insert_to_test_job;',sysdate,'sysdate+1/ (24060)'); 3 commit; 4 end; 5 / PL/SQL procedure successfully completed.SQL > select * from test_job Xmi 2015-07-27 14:41:17SQL > select job,to_char (last_date,'yyyy-mm-dd hh34:mi:ss') last_date,to_char (next_date,'yyyy-mm-dd hh34:mi:ss') next_date,interval,what from user_jobs JOB LAST_DATE NEXT_DATE INTERVAL WHAT- 3 2015-07-27 14:47:17 2015-07- 27 14:48:17 sysdate+1/ (24060) p_insert_to_test_job SQL > select job,what,failures,broken from user_jobs; JOB WHAT FAILURES B-3 packs insertinserted to complete testworthy jobs; 0 N SQL > execute dbms_job.remove (3) (delete job) PL/SQL procedure successfully completed.SQL > select job,what,failures,broken from user_jobs; no rows selectedSQL > select job,to_char (last_date,'yyyy-mm-dd hh34:mi:ss') last_date,to_char (next_date,'yyyy-mm-dd hh34:mi:ss') next_date,interval,what from user_jobs; no rows selectedSQL > select count (*) from test_job COUNT (*)-13 SQL > begin (rerun) dbms_job.submit (: job_num,'p_insert_to_test_job;',sysdate,'sysdate+1/ (24060)'); commit; end; / 23 45 PL/SQL procedure successfully completed.SQL > select count (*) from test_job COUNT (*)-15 SQL > select job,what,failures,broken from user_jobs; JOB WHAT FAILURES B-4 packs insertinsertinsert to testworthy jobs; 0 N

To be continued.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.