Oracle job interval setting

Time:2020-11-13

Initializing related parameters job_ queue_ processes

alter system set job_ queue_ Processes = 39 scope = SPFILE; / / the maximum value cannot exceed 1000; job_ queue_ Interval = 10 / / refresh rate of scheduling jobs in seconds

job_ queue_ Process indicates the number of concurrent jobs that Oracle can use

show parameter job_queue_process;

select * from v$parameter where name=’job_queue_processes’;

To view the jobs in Oracle_ queue_ The value of process. When job_ queue_ When the process value is 0, it means that all Oracle jobs will be stopped. You can use the

ALTER SYSTEM SET job_queue_processes = 10;

To adjust the starting Oracle job.

Related views:

dba_jobs
all_jobs
user_jobs
dba_ jobs_ Running contains information about running jobs

SQL for checking jobs:

select job,last_date,last_sec,next_date,next_sec,INTERVAL,WHAT from dba_jobs order by job;

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

Submit job syntax:

Copy codeThe code is as follows:
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 job

Copy codeThe code is as follows:
variable jobno number;
begin
dbms_job.submit(:jobno, ‘P_CRED_PLAN;’,SYSDATE,’SYSDATE+1/2880′,TRUE);
commit;

Run job

Copy codeThe code is as follows:
SQL> begin
         dbms_job.run(:job1);
         end;
         /

Delete job

Copy codeThe code is as follows:
SQL> begin
        dbms_job.remove(:job1);
        end;
        /

DBA_JOBS

===========================================

Field (column) type describe
JOB NUMBER Unique identification number of the task
LOG_USER VARCHAR2(30) The user who submitted the task
PRIV_USER VARCHAR2(30) Users who are given task permissions
SCHEMA_USER VARCHAR2(30) User mode for task parsing
LAST_DATE DATE The last time the task ran successfully
LAST_SEC VARCHAR2(8) For example, last in hh24: mm: SS format_ The hour, minute, and second of the date
THIS_DATE DATE The start time of the running task, null if no task is running
THIS_SEC VARCHAR2(8) For example, hh24: mm: SS format of this_ The hour, minute, and second of the date
NEXT_DATE DATE The next time the task is scheduled to run
NEXT_SEC VARCHAR2(8) For example, hh24: mm: SS format next_ The hour, minute, and second of the date
TOTAL_TIME NUMBER The total time, in seconds, required for the task to run
BROKEN VARCHAR2(1) Flag parameter, y indicates that the task is interrupted and will not run in the future
INTERVAL VARCHAR2(200) The expression used to calculate the next run time
FAILURES NUMBER The number of consecutive unsuccessful task runs
WHAT VARCHAR2(2000) PL / SQL block that performs the task
CURRENT_SESSION_LABEL RAW MLSLABEL The trusted Oracle session character for the task
CLEARANCE_HI RAW MLSLABEL The maximum Oracle gap that the task can trust
CLEARANCE_LO RAW MLSLABEL Minimum Oracle gap that the task can trust
NLS_ENV VARCHAR2(2000) NLS session settings for the task to run
MISC_ENV RAW(32) Some other session parameters for the task to run

--------------------------
Describes the value of the interval parameter
Every day at midnight ‘TRUNC (sysdate + 1)’
Every morning at 8:30am’trunc (sysdate + 1) + (8 * 60 + 30) / (24 * 60) ‘
Every Tuesday at 12:00 noon ‘next’_ DAY(TRUNC(SYSDATE ), ”TUESDAY” ) + 12/24’
At midnight on the first day of each month_ DAY(SYSDATE ) + 1)’
At 11:00 p.m. on the last day of each quarter ‘TRUNC (add_ MONTHS(SYSDATE + 2/24, 3 ), ‘Q’ ) -1/24′
Every Saturday and Sunday at 6:10 am_ DAY(SYSDATE, ”SATURDAY”), NEXT_ DAY(SYSDATE, “SUNDAY”))) + (6×60+10)/(24×60)’

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

1: Execution per minute

Interval => TRUNC(sysdate,’mi’) + 1/ (24*60)

2: Regular execution every day

For example: every day at 1:00 a.m

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

3: Regular weekly execution

For example: every Monday at 1:00 a.m

Interval => TRUNC(next_ Day (sysdate, ‘Monday’) + 1 / 24

4: Regular execution every month

For example: on the first day of every month at 1:00 a.m

Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24

5: Regular execution every quarter

For example, it is implemented at 1:00 a.m. on the first day of each quarter

Interval => TRUNC(ADD_MONTHS(SYSDATE,3),’Q’) + 1/24

6: Regular implementation every half a year

For example: 1:00 a.m. on July 1 and January 1 every year

Interval => ADD_MONTHS(trunc(sysdate,’yyyy’),6)+1/24

7: It is carried out regularly every year

For example, it is implemented at 1:00 a.m. on January 1 every year

Interval =>ADD_MONTHS(trunc(sysdate,’yyyy’),12)+1/24