Learn how to create a job in an Oracle environment

Create a job in Oracle:

Create table date_log (create_date date constraint create_date_pk primary key );

Create or replace procedure create_date_log_row
Is
Begin
Insert into date_log (create_date) values (sysdate );
End;
/

Call create_date_log_row regularly to insert data in the date_log table.

Job_queue_processes = 2: Number of processes caused by job execution (minimum value: 0, maximum value: 36) 0 indicates that the job is not run.

Job_queue_interval = 30: The value range is 1 ~ Between 3600, in seconds. Wake up the process regularly to determine whether a job is to be executed. The time here is 30 seconds.

Alter system set Job_queue_processes = 0;

Alter system set Job_queue_processes = 5;

Dbms_job.submit (: jobno, 'create _ date_log_row ', trunc (sysdate) + 1/24, 'trunc (sysdate) + 1/24 + 1 ')

Jobno: job ID

Create_date_log_row: name of the stored procedure to be executed

Trunc (sysdate) + 1/24: next execution time

'Trunc (sysdate) + 1/24 + 1': interval

Job: dbms_job.remove (jobno); remove the job number

Job: dbms_job.what (jobno, what); Modify the job number

Dbms_job.next_date (job, next_date): modifies the next execution time.

Dbms_job.interval (job, interval): interval of Modification
 
Job: dbms_job.broken (job, true): Stop job

Job: dbms_job.broken (job, false, next_date) next_date: next execution time. If not specified, start the job immediately.

Job: dbms_job.run (jobno );

Step 1: create a task

SQL> VAR job1 number; -- store the job number

SQL> begin
Dbms_job.submit (: job1, 'create _ date_log_row; ', sysdate, 'sysdate + 100 ');
Commit;
End;

SQL> PRINT job1;

Select * from date_log;

-- View the job parameter value

Select last_date, last_sec, next_date, next_sec, interval, broken from user_jobs;

-- Stop a running job

Exec dbms_job.broken (46, true );
Commit;
 
Select broken from user_jobs where job = 46;

-- Start job

Exec dbms_job.broken (46, false );
Commit;

--
Select next_date, next_sec from user_jobs;

Modify the next execution time:

Exec dbms_job.next_date (5, sysdate + 2/(24*60 ));
Commit;

-- Modify the execution frequency

Exec dbms_job.interval (5, sysdate + 3/(24*60 ));
Commit;

-- Remove job
Job: dbms_job.remove (46 );

-- Execution time setting:

'Trunc (sysdate + 1) 'At midnight every day )'
Every morning 08:30 'trunc (sysdate + 1) + (1*60 + 30)/(24*60 )'
'Next _ day (trunc (sysdate), "Tuesday") + 123' at every Tuesday'
'Trunc (last_day (sysdate) + 1 )'
'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) Every SATURDAY and 06:10 a.m) /(24*60 )'