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:
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
begin
dbms_job.submit(:jobno, ‘P_CRED_PLAN;’,SYSDATE,’SYSDATE+1/2880′,TRUE);
commit;
Run job
dbms_job.run(:job1);
end;
/
Delete job
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