Oracle job example

Declare
X number;
Begin
SYS. dbms_job.submit
(Job => X,
What => 'etl _ run_d_date ;',
Next_date => to_date ('2017-08-26 01:00:00 ', 'yyyy-mm-dd hh24: MI: ss '),
Interval => 'trunc (sysdate) + 1 + 123 ',
No_parse => false
);
SYS. dbms_output.put_line ('job number is: '| to_char (x ));
Commit;
End;
/The preceding statement explicitly specifies that the job is executed at every day. If you specify to execute interval at every day, you must specify it as 'trunc (sysdate) + 1 + 123 ', if you only specify interval as one day, the daily execution time of a job changes when you manually run the job once with dbms_job.run (job, if you want to execute a job at a fixed time every day, refer to the above example.

 

Initialize related parameters job_queue_processes
Alter system set job_queue_processes = 39 scope = spfile; // The maximum value cannot exceed 1000; job_queue_interval = 10 // The refresh frequency of the scheduled job is measured in seconds.

 

Job_queue_processOracleNumber of concurrent jobs.

Show parameter job_queue_process;

To view the value of job_queue_process in Oracle. When job_queue_process is set to 0, all Oracle jobs are stopped.

Alter system set job_queue_processes = 10;

To adjust the Oracle startup job.

Related views:
Dba_jobs
All_jobs
User_jobs
Dba_jobs_running contains information about running jobs.

 

-------------------------

Submit job Syntax:

Begin
SYS. dbms_job.submit (job =>: Job,
What => 'P _ clear_packbal ;',
Next_date => to_date ('04-08-2008 05:44:09 ', 'dd-mm-yyyy hh24: MI: ss '),
Interval => 'sysdate + 1/360 ');
Commit;
End;
/

 

-------------------------
Create a job
Variable jobno number;
Begin
Dbms_job.submit (: jobno, 'P _ cred_plan; ', sysdate, 'sysdate + 1/2880', true );
Commit;

 

Run job
SQL> begin
Dbms_job.run (: job1 );
End;
/

 

Delete a job
SQL> begin
Dbms_job.remove (: job1 );
End;
/

 

Dba_jobs
========================================================== ===
Field (column) type description
Unique ID of a job number
Log_user varchar2 (30) user who submits the task
Priv_user varchar2 (30) user with task Permissions
Schema_user varchar2 (30) User Mode for Syntactic Analysis of tasks
Last_date date the time when the last task was successfully run
Last_sec varchar2 (8) such as hh24: mm: hour, minute, and second of the SS format last_date
The start time of the task being run by this_date. If no task is running, the value is null.
This_sec varchar2 (8) such as hh24: mm: the hour, minute, and second of the this_date date in SS format
Next_date date the next scheduled task running time
Next_sec varchar2 (8) such as hh24: mm: the hour, minute, and second of the SS format next_date
Total_time number the total time required to run the task, in seconds
The broken varchar2 (1) Flag parameter. y indicates that the task is interrupted and will not run later.
Interval varchar2 (200) is the expression used to calculate the next line time.
Failures number the number of times the task failed to run continuously
What varchar2 (2000) PL/SQL block for task execution
Current_session_label raw mlslabel trust Oracle session character of the task
Clearance_hi raw mlslabel maximum gap between Oracle databases trusted by this task
Clearance_lo raw mlslabel minimum Oracle gap trusted by this task
NLS session settings for running the nls_env varchar2 (2000) task
Other session parameters of the misc_env raw (32) task

 

--------------------------
Parameter Value of Interval
'Trunc (sysdate + 1) 'At midnight every day )'
Every morning 08:30 'trunc (sysdate + 1) + (8*60 + 30)/(24*60 )'
Next _ day (trunc (sysdate), ''tuesday'') + 12/24 at every Tuesday'
'Trunc (last_day (sysdate) + 1) 'at midnight on the first day of each month )'
'Trunc (add_months (sysdate + 2/24, 3), 'q')-100' at on the last day of each quarter'
'Trunc (least (next_day (sysdate, ''saturday "), next_day (sysdate," Sunday ") + (6 × 60 + 10) /(24 × 60 )'

 

--------------------------

1: executed per minute

Interval => trunc (sysdate, 'mi') + 1/(24*60)

Or

Interval = & gt; sysdate + 1/1440

2: daily scheduled execution

Example: Execute at every day

Interval => trunc (sysdate) + 1 + 1/(24)

3: scheduled weekly execution

Example: Execute at every Monday

Interval => trunc (next_day (sysdate, 'monday') + 1/24

4: scheduled monthly execution

Example: Execute at on the first day of every month

Interval => trunc (last_day (sysdate) + 1 + 1/24

5: Periodical execution on a quarterly basis

For example, the statement is executed at on the first day of each quarter.

Interval => trunc (add_months (sysdate, 3), 'q') + 1/24

6: scheduled execution every six months

For example, at a.m. on January 1, July 1 and January 1, January 1

Interval => add_months (trunc (sysdate, 'yyyy'), 6) + 1/24

7: scheduled execution every year

Example: Execute at on January 1, January 1 every year.

Interval => add_months (trunc (sysdate, 'yyyy'), 12) + 1/24

/////////////////////////////

Begin
SYS. dbms_job.submit (job =>: Job,
What => 'check _ err ;',
Next_date => trunc (sysdate) plus 23/24,
Interval => 'trunc (next_day (sysdate, ''friday'') + 1000 ');
Commit;
End;

Here: job is a process automatically generated by the system, and check_err is one of my processes. next_date sets the next execution time, which is this evening. interval sets the interval and the execution time, this is on Friday every week. The function next_day returns the date containing the specified character. The trunc function removes the time in the date, that is, the of a day, the time is in the unit of days. Therefore, if you want to score a certain point, you need the score:

1/24 hour;
1/1440 points;
1/3600 seconds;

/////////////////////

1. Set the initialization parameter job_queue_processes.
SQL> alter system set job_queue_processes = N; (n> 0)
Job_queue_processes: the maximum value is 1000.

View the Job Queue background process
SQL> select name, description from V $ bgprocess;

2. dbms_job package usage
It contains the following sub-processes:

Broken () process.
Change () process.
Interval () process.
Isubmit () process.
Next_date () process.
Remove () process.
Run () process.
Submit () process.
User_export () process.
What () process.

1. The broken () process updates the status of a submitted job. It is typically used to mark a broken job as a non-broken job.
This process has three parameters: Job, broken, and next_date.

Procedure broken (job in binary_integer,
Broken in Boolean,
Next_date in date: = sysdate)

The job parameter is the work number, which uniquely identifies the work in the problem.
The broken parameter indicates whether the job is marked as broken -- true indicates that the job is marked as broken, and flase indicates that the job is marked as not broken.
The next_date parameter indicates when the job will run again. The default value of this parameter is the current date and time.
If a job fails for some reason, Oracle will retry 16 times and fail to execute successfully. It will be marked as a job in broken restart status, there are two methods;
A. Use dbms_job.run () to execute the job immediately.
SQL> begin
SQL> dbms_job.run (: jobno) The jobno is the job number returned when the submit process is submitted.
SQL> end;
SQL>/
B. Use dbms_job.broken () to mark the broken as false again.
SQL> begin
SQL> dbms_job.broken (: Job, false, next_date)
SQL> end;
SQL>/
2. The change () process is used to change the settings of a specified job.
This process has four parameters: job, what, next_date, and interval.

Procedure change (job in binary_integer,
What in varchar2,
Next_date in date,
Interval in varchar2)

The job parameter is an integer that uniquely identifies the job.
What parameter is a PL/SQL statement Code Block.
The next_date parameter indicates when the job will be executed.
The interval parameter indicates the frequency of re-execution.

3. The interval () process is used to explicitly set the time interval between re-execution of a job. This process has two parameters: job and interval.

Procedure interval (job in binary_integer,
Interval in varchar2)

The job parameter identifies a specific job. The interval parameter indicates the frequency of re-execution.

4. The isubmit () process is used to submit a job with a specific job number. This process has five parameters: job, what, next_date, interval, and no_parse.

Procedure isubmit (job in binary_ineger,
What in varchar2,
Next_date in date,
Interval in varchar2,
No_parse in booean: = false)

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

5. The next_date () process is used to explicitly set the execution time of a job. This process receives two parameters: job and next_date.

Procedure next_date (job in binary_ineger,
Next_date in date)
A job identifies an existing job. The next_date parameter indicates the date and time when the job should be executed.

6. Remove () to delete a scheduled job. This process receives a parameter:

Procedure remove (job in binary_ineger );

The job parameter uniquely identifies a job. The value of this parameter is the value of the job parameter returned when submit () is called for this job. A running job cannot be called Program Delete.

7. The run () process is used to immediately execute a specified job. This process only receives one parameter:

Procedure run (job in binary_ineger)

The job that will be executed immediately.

8. Use the submit () process and the work is properly planned.
This process has five parameters: job, what, next_date, interval, and no_parse.

Procedure submit (job out binary_ineger,
What in varchar2,
Next_date in date,
Interval in varchar2,
No_parse in booean: = false)

The job parameter is the binary_ineger returned by the submit () process. This value uniquely identifies a job.
What parameter is the PL/SQL code block to be executed.
The next_date parameter specifies when the job will be run.
The interval parameter indicates when the job will be re-executed.
The no_parse parameter indicates whether the job should perform syntax analysis at the time of submission or execution -- true indicates that the PL/SQL code should perform syntax analysis during its first execution, false indicates that the PL/SQL code should be analyzed immediately.

9. A command is returned during the user_export () process. This command is used to schedule an existing job so that the job can be submitted again.
This program has two parameters: job and my_call.

Procedure user_export (job in binary_ineger,
My_call in out varchar2)

The job parameter identifies a scheduled job. The my_call parameter contains the body required to resubmit the job in its current state.

10. What () process promises to reset the running command during job execution. This process receives two parameters: job and what.

Procedure what (job in binary_ineger,
What in out varchar2)
The job parameter identifies an existing job. What parameter indicates the new PL/SQL code to be executed.

3. View related job information
1. Related views
Dba_jobs
All_jobs
User_jobs
Dba_jobs_running contains information about running jobs.

2. View related information

SQL> select job, next_date, next_sec, failures, broken
SQL> from dba_jobs;

Job next_date next_sec failures B
---------------------------------
9125 01-jun-01 00:00:00 4 N
14144 24--01 -01 16:35:35 0 n
9127 01-jun-01 00:00:00 16 Y
3 rows selected.

Information about running jobs

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;

Sid job log_user this_date this_sec
---------------------------------------------
12 14144 HR 24-oct-94 17:21:24
25 8536 QS 24-oct-94 16:45:12
2 rows selected.

Job Queue lock Information

Select Sid, type, id1, Id2
From v $ lock
Where type = 'jq ';

Sid ty id1 Id2
-----------------------------
12 JQ 0 14144
1 row selected.

Iv. Simple examples
A simple example:

Create test table
SQL> Create Table Test (a date );

The table has been created.

Create a custom Process
SQL> Create or replace procedure myproc
2 begin
3 insert into test values (sysdate );
4 end;
5/

The process has been created.

Create a job
SQL> variable job1 number;
SQL>
SQL> begin
2 dbms_job.submit (: job1, 'myproc; ', sysdate, 'sysdate + 100'); -- 1/1440 minutes a day, that is, one minute to run the test process
3 end;
4/

The PL/SQL process is successfully completed.

Run job
SQL> begin
2 dbms_job.run (: job1 );
3 end;
4/

The PL/SQL process is successfully completed.

SQL> select to_char (A, 'yyyy/MM/DD hh24: MI: ss') time from test;

Time
-------------------
2001/01/07 23:51:21
2001/01/07 23:52:22
2001/01/07 23:53:24

Delete a job
SQL> begin
2 dbms_job.remove (: job1 );
3 end;
4/

The PL/SQL process is successfully completed.