创建定时器
declare
jobno number;
begin dbms_job.submit(
jobno,--取数据库增量值,可指定数字,定时器唯一标识
'p_dosomething(''a'');'
, --what
to_date(
'20090101020000'
,
'yyyy-mm-dd hh24:mi:ss'
),--next_date,
可下一次执行时间
'Interval
时间字符串
'
--interval(时间间隔)
);
commit;
end;
select * from user_jobs;——查看当前用户定时器
select * from dba_jobs;——查看所有用户定时器
Column | Datatype | NULL | Description |
JOB | NUMBER | NOT NULL | 唯一标识 |
LOG_USER | VARCHAR2(30) | NOT NULL | Login user when the job was submitted |
PRIV_USER | VARCHAR2(30) | NOT NULL | User whose default privileges apply to this job |
SCHEMA_USER | VARCHAR2(30) | NOT NULL | Default schema used to parse the jobFor example, if the SCHEMA_USERis SCOTT and you submit the procedure HIRE_EMP as a job, the Oracle Database looks forSCOTT.HIRE_EMP |
NEXT_DATE | DATE | NOT NULL | Date that this job will next be executed |
BROKEN | VARCHAR2(1) | Y: no attempt is made to run this job N: an attempt is made to run this job | |
INTERVAL | VARCHAR2(200) | NOT NULL | A date function, evaluated at the start of execution, becomes nextNEXT_DATE |
FAILURES | NUMBER | Number of times the job has started and failed since its last success | |
WHAT | VARCHAR2(4000) | Body of the anonymous PL/SQL block that the job executes | |
NLS_ENV | VARCHAR2(4000) | Session parameters describing the NLS environment of the job |
select * from dba_jobs_running;
select * from dba_scheduler_jobs;
DBMS_JOB.ISUBMIT 提交一个新任务,用户指定一个任务号
DBMS_JOB.SUBMIT 提交一个新任务,系统指定一个任务号
DBMS_JOB.REMOVE 从队列中删除一个已经存在的任务
DBMS_JOB.CHANGE 更改用户设定的任务参数
DBMS_JOB.WHAT 更改PL/SQL任务定义
DBMS_JOB.NEXT_DATE 更改任务下一次运行时间
DBMS_JOB.INTERVAL 更改任务运行的时间间隔
DBMS_JOB.BROKEN 将任务挂起,不让其重复运行
DBMS_JOB.RUN 在当前会话中立即执行任务
DBMS_JOB.SUBMIT 提交一个新任务,系统指定一个任务号
DBMS_JOB.REMOVE 从队列中删除一个已经存在的任务
DBMS_JOB.CHANGE 更改用户设定的任务参数
DBMS_JOB.WHAT 更改PL/SQL任务定义
DBMS_JOB.NEXT_DATE 更改任务下一次运行时间
DBMS_JOB.INTERVAL 更改任务运行的时间间隔
DBMS_JOB.BROKEN 将任务挂起,不让其重复运行
DBMS_JOB.RUN 在当前会话中立即执行任务
DBMS_JOB.USER_EXPORT 创建文字字符串,用于重新创建一个任务
定时器执行错误会隔一段时间重新执行,执行间隔从2分钟、4分钟、8分钟、直到24小时,以后都以24小时为周期执行,直到执行成功