Job in Oracle

Oracle Knowledge Point-job

Oracle The job in crontab is similar to the role of a Linux-A to perform certain operations on a timed

Related views: dba_jobs,user_jobs,all_jobs,dba_jobs_running

Related parameters:job_queue_processes

Related packages: Dbms_job

For an explanation of the Dba_jobs, dba_jobs_running fields:

Dba_jobs describe all jobs in the database

Desc dba_jobs

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/70/97/wKioL1W52aSwu4fDAAUQCnC9OCs827.jpg "title=" 2015-07-30_154834.png "alt=" Wkiol1w52aswu4fdaauqcnc9ocs827.jpg "/>

Values for the interval parameter

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/70/97/wKioL1W52djA1Y4SAAKnAwGEZUw005.jpg "title=" 2015-07-30_154858.png "alt=" Wkiol1w52dja1y4saaknawgezuw005.jpg "/>

Dba_jobs_running lists all running jobs in the current instance

Desc dba_jobs_running

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/70/9A/wKiom1W51_qjtjUYAAHqsLZekzU668.jpg "title=" 2015-07-30_154907.png "alt=" Wkiom1w51_qjtjuyaahqslzekzu668.jpg "/>

Explanation of Job_queue_processes:

specifies that a single instance executes the maximum number of dbms_job jobs and Oracle Scheduler (dbms_scheduler) jobs. If job_queue_processes is set to 0,dbms_job jobs and Oracle Scheduler will not run on the instance. The value range is 0-1000.

explanations for the Dbms_job package:

Dbms_job contains sub-processes broken (), change (), instance (), interval (), Isubmit (), Next_date (), remove (), run (), submit (), user _export (), what ()

1.broken () Span style= "font-size:19px;font-family: ' italics ';" >

PROCEDURE broken Argument nametypein/out Default? -------------------------------------------------------------------Jobbinary_integerin broken BOOLEAN in Next_ Datedatein DEFAULT

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

The broken parameter indicates whether this work will be marked as broken--true this work will be marked as broken, flase stating that the work will be marked as not broken.

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

If the job fails for any reason, Oracle will retry 16 times, and will be marked as broken restart job with broken status.

2.ch Ange () over process to change the settings of the specified work

procedure change argument nametypein/out default?  ------------------------------ ----------------------- ------ -------- jobbinary_ integerin whatvarchar2in next_datedatein intervalvarchar2in instancebinary_integerin      default forceboolean in     default 

The job parameter is an integer value that uniquely identifies the job.
What parameter is a PL/SQL code block that is run by this operation.
The Next_date parameter indicates when the work will be executed.
The interval parameter indicates the frequency of a work re-execution.

3.instance () process

PROCEDURE INSTANCE Argument nametypein/out Default? -------------------------------------------------------------------Jobbinary_integerin Instancebinary_integerin Forceboolean in DEFAULT

4.interval () process used to explicitly set the number of intervals between re-executing a work

PROCEDURE INTERVAL Argument nametypein/out Default? -------------------------------------------------------------------Jobbinary_integerin intervalvarchar2in

The interval parameter indicates the frequency of a work re-execution

5.isubmit () process used to submit a job with a specific work number

PROCEDURE isubmit Argument nametypein/out Default? -------------------------------------------------------------------Jobbinary_integerin whatvarchar2in next_ Datedatein intervalvarchar2in default No_parseboolean in default

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

6.next_date () Process used to explicitly set the execution time of a job

FUNCTION is_jobq RETURNS booleanprocedure next_date Argument nametypein/out Default? -------------------------------------------------------------------Jobbinary_integerin Next_datedatein

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

7.remove () process used to delete a work that has been scheduled to run

PROCEDURE REMOVE Argument nametypein/out Default? -------------------------------------------------------------------Jobbinary_integerin

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

8.run () process used to perform a specified job immediately

PROCEDURE RUN Argument nametypein/out Default? -------------------------------------------------------------------Jobbinary_integerin Forceboolean in DEFAULT

9.submit () process work is properly planned.

procedure submit argument nametypein/out default?  ------------------------------ ----------------------- ------ -------- jobbinary_ integerout whatvarchar2in next_datedatein     default  intervalvarchar2in     default no_parseboolean in      default instancebinary_integerin     default forceboolean in      default 

      job parameter is returned by the submit () procedure Binary_ Ineger. This value is used to uniquely identify a job.
WHA The T parameter is a PL/SQL code block that will be executed.
NEX The T_date parameter indicates when the job will run.
int Erval parameter when this work will be re-executed.
no The _parse parameter indicates whether this work should be parsed at the time of submission or execution--true indicates that this         PL/SQL code should parse the first time it executes, and false to indicate that the pl/ The SQL code should be parsed immediately.

10.user_export () process returns a command that is used to schedule an existing work so that the work can be resubmitted

PROCEDURE user_export Argument nametypein/out Default? -------------------------------------------------------------------Jobbinary_integerin Mycall varchar2in/out

The My_call parameter contains the body that is required to resubmit this work in its current state

11.user_export () process

PROCEDURE user_export Argument nametypein/out Default? -------------------------------------------------------------------Jobbinary_integerin Mycall varchar2in/out Myinst Varchar2in/out

12.what () process

PROCEDURE what Argument nametypein/out Default? -------------------------------------------------------------------Jobbinary_integerin whatvarchar2in

What parameter indicates the new PL/SQL code that will be executed.

View job information under the current user

sql> show useruser is  "HR" Sql> select sid, type, id1, id2  FROM V$LOCK WHERE TYPE =  ' JQ '; (View job lock related information) 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 that the current user is running)  no rows selectedsql> alter system set job_queue_ processes = 20 ;  (Sets the number of job queues) system altered. sql> show parameter job_queue_processesname     type  VALUE------------------------------------ ----------- ------------------------------Job_queue_ Processes     integer 20

job Create and test

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 /(24*60) ');   3  commit ;  4  end ;  5  / Pl/sql procedure successfully completed. sql> select * from test_job ; X-------------------2015-07-27 14:41:17sql> select job,to_char (last_date, ' yyyy-mm-dd  Hh24:mi:ss ')  last_date,to_char (next_date, ' yyyy-mm-dd hh24: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/(24*60)  p_insert_to_test_job;      SQL>  select job,what,failures,broken from user_jobs ;        Job what      failures b---------- ----------------------- - --------- - 3 p_insert_to_test_job;    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 selected  sql> selEct job,to_char (last_date, ' Yyyy-mm-dd hh24:mi:ss ')  last_date,to_char (next_date, ' yyyy-mm-dd  hh24:mi:ss ')  next_date,interval,what from user_jobs ;no rows selectedsql > select count (*)  from test_job ;  count (*)----------13SQL>  begin   (Rerun) dbms_job.submit (: Job_num, ' p_insert_to_test_job; ', Sysdate, ' sysdate+1/(24*60) '); commit  ;end ;/  2    3    4    5   pl/sql procedure successfully completed. Sql> select count (*)  from test_job ;  count (*)----------15SQL>  select job,what,failures,broken from user_jobs ;        Job what      failures b---------- ----------------------- - --------- - 4 p_insert_to_test_job;   &nbsP;0 n 

Not to be continued ...

This article is from "The girl said" blog, please be sure to keep this source http://sugarlovecxq.blog.51cto.com/6707742/1680152

Job in Oracle