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 //Scheduling job refresh frequency in seconds
job_queue_process represents the number of jobs that Oracle can run concurrently, which can be passed through the statement
show parameter job_queue_process;
To view the value of job_queue_process in oracle. When the job_queue_process value is 0, it means to stop all Oracle jobs. You can pass the statement
ALTER SYSTEM SET job_queue_processes = 10;
To adjust the job that starts Oracle.
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 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 JOB
SQL> begin
dbms_job.remove(:job1);
end;
/
DBA_JOBS
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
Field (column) Type Description
JOB NUMBER The unique identification number of the task
LOG_USER VARCHAR2(30) The user who submitted the task
PRIV_USER VARCHAR2(30) User assigned task permissions
SCHEMA_USER VARCHAR2(30) User mode for parsing tasks
LAST_DATE DATE The last time the task was successfully run
LAST_SEC VARCHAR2(8) Hour, minutes and seconds of last_date date in HH24:MM:SS format
THIS_DATE DATE The start time of the running task, or null if no task is running
THIS_SEC VARCHAR2(8) Hour, minutes and seconds of this_date date in HH24:MM:SS format
NEXT_DATE DATE The next time the scheduled task will be run
NEXT_SEC VARCHAR2(8) Hour, minutes and seconds of next_date date in HH24:MM:SS format
TOTAL_TIME NUMBER The total time required for the task to run, in seconds
BROKEN VARCHAR2(1) flag parameter, Y indicates that the task is interrupted and will not run in the future.
INTERVAL VARCHAR2(200) Expression used to calculate the next run time
FAILURES NUMBER The number of times the task has been run without success.
WHAT VARCHAR2(2000) PL/SQL block that executes the task
CURRENT_SESSION_LABEL RAW MLSLABEL The trusted Oracle session key for this task
CLEARANCE_HI RAW MLSLABEL The maximum Oracle gap that the task can trust
CLEARANCE_LO RAW MLSLABEL The Oracle minimum gap that the task can trust
NLS_ENV VARCHAR2(2000) NLS session settings for task running
MISC_ENV RAW(32) Some other session parameters for task running
－－－－－－－－－－－－－－－－－－－－－－－－－
Description INTERVAL parameter value
'TRUNC(SYSDATE + 1)' every day at 12 midnight
Every morning at 8:30 'TRUNC(SYSDATE + 1) + (8*60+30)/(24*60)'
Every Tuesday at 12 noon 'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24'
'TRUNC(LAST_DAY(SYSDATE ) + 1)' at 12 midnight on the first day of each month
11pm 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 'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + (6×60+10)/(24×60)'
－－－－－－－－－－－－－－－－－－－－－－－－－
1: Execute every minute
Interval => TRUNC(sysdate,'mi') + 1/ (24*60)
2: Execute regularly every day
For example: executed at 1 am every day
Interval => TRUNC(sysdate) + 1 +1/ (24)
3: Scheduled execution every week
For example: executed every Monday at 1 am
Interval => TRUNC(next_day(sysdate,'Monday'))+1/24
4: Regular execution every month
For example: Execute at 1 am on the 1st of each month
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24
5: Regular execution every quarter
For example, it is executed at 1 a.m. on the first day of each quarter.
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24
6: Regular execution every six months
For example: every July 1st and January 1st at 1 a.m.
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24
7: Executed regularly every year
For example: Executed at 1am on January 1st every year
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24
This article comes from the CSDN blog. Please indicate the source when reprinting: http://blog.csdn.net/mzwang123/archive/2009/12/22/5053476.aspx
-