Oracle定时作业.docx
《Oracle定时作业.docx》由会员分享，可在线阅读，更多相关《Oracle定时作业.docx（12页珍藏版）》请在冰豆网上搜索。
Oracle定时作业
Oracle定时作业
1准备
1．确保Oracle的工作模式允许启动任务队列管理器
Oracle定时执行“JobQueue”的后台程序是SNP进程，而要启动SNP进程，首先要确保整个系统的模式是可以启动SNP进程的，这需要以DBA的身份去执行如下命令：
svrmgrl>;altersystemenablerestrictedsession;
或sql>;altersystemdisenablerestrictedsession;
利用如上命令更改系统的会话方式为disenablerestricted，为SNP的启动创造条件。
2．确保Oracle的系统已经配置了任务队列管理器的启动参数
SNP的启动参数位于Oracle的初始化文件中，该文件放在＄ORACLE_HOME/dbs路径下，如果Oracle的SID是myora8的话，则初始化文件就是initmyora8.ora，在文件中对SNP启动参数的描述部分如下：
job_queue_process=n
job_queue_interval=N
第一行定义SNP进程的启动个数为n。
系统缺省值为0，正常定义范围为0～36，根据任务的多少，可以配置不同的数值。
第二行定义系统每隔N秒唤醒该进程一次。
系统缺省值为60秒，正常范围为1～3600秒。
事实上，该进程执行完当前任务后，就进入睡眠状态，睡眠一段时间后，由系统的总控负责将其唤醒。
如果该文件中没有上面两行，请按照如上配置添加。
配置完成后，需要重新启动数据库，使其生效。
注意：
如果任务要求执行的间隔很短的话，N的配置也要相应地小一点。
sql>altersystemsetjob_queue_processes=n;（n>0）
job_queue_processes最大值为1000
查看jobqueue后台进程
sql>selectname,descriptionfromv$bgprocess;
或者sql>showparameterjob_queue_processes;
3．确保执行任务的数据库用户有权限
将任务加入到任务队列之前，要确定执行任务的数据库用户，若用户是scott,则需要确保该用户拥有执行包dbms_job的权限；若没有，需要以DBA的身份将权利授予scott用户：
svrmgrl>;grantexecuteondbms_jobtoscott;
2语法
2.1创建作业
将要执行的任务写成存储过程或其他的数据库可执行的pl/sql程序段
dbms_job.submit（joboutbinary_integer,
what in varchar2,
next_date in date，
interval in varchar2,
no_parse in boolean）
参数说明：
●job：
输出变量，是此任务在任务队列中的编号；
●what：
执行的任务的名称及其输入参数；
●next_date：
任务执行的时间；
●interval：
任务执行的时间间隔。
严格地讲，interval是指上一次执行结束到下一次开始执行的时间间隔，当interval设置为null时，该job执行结束后，就被从队列中删除。
假如我们需要该job周期性地执行，则要用‘sysdate＋m’表示。
INTERVAL参数值
描述INTERVAL参数值
每天午夜12点'TRUNC（SYSDATE+1）'
每天早上8点30分'TRUNC（SYSDATE+1）+（8*60+30）/（24*60）'
每星期二中午12点'NEXT_DAY（TRUNC（SYSDATE）,''TUESDAY''）+12/24'
每个月第一天的午夜12点'TRUNC（LAST_DAY（SYSDATE）+1）'
每个季度最后一天的晚上11点'TRUNC（ADD_MONTHS（SYSDATE+2/24,3）,'Q'）-1/24'
每星期六和日早上6点10分'TRUNC（LEAST（NEXT_DAY（SYSDATE,''SATURDAY"）,NEXT_DAY（SYSDATE,"SUNDAY"）））+（6×60+10）/（24×60）'
－－－－－－－－－－－－－－－－－－－－－－－－－－
描述INTERVAL参数值
每天午夜12点'TRUNC（SYSDATE+1）'
每天早上8点30分'TRUNC（SYSDATE+1）+（8*60+30）/（24*60）'
每星期二中午12点'NEXT_DAY（TRUNC（SYSDATE）,''TUESDAY''）+12/24'
每个月第一天的午夜12点'TRUNC（LAST_DAY（SYSDATE）+1）'
每个季度最后一天的晚上11点'TRUNC（ADD_MONTHS（SYSDATE+2/24,3）,'Q'）-1/24'
每星期六和日早上6点10分'TRUNC（LEAST（NEXT_DAY（SYSDATE,''SATURDAY"）,NEXT_DAY（SYSDATE,"SUNDAY"）））+（6×60+10）/（24×60）'
－－－－－－－－－－－－－－－－－－－－－－－－－－
1:
每分钟执行
Interval=>TRUNC（sysdate,'mi'）+1/（24*60）
2:
每天定时执行
例如：
每天的凌晨1点执行
Interval=>TRUNC（sysdate）+1+1/（24）
3:
每周定时执行
例如：
每周一凌晨1点执行
Interval=>TRUNC（next_day（sysdate,'星期一'））+1/24
4:
每月定时执行
例如：
每月1日凌晨1点执行
Interval=>TRUNC（LAST_DAY（SYSDATE））+1+1/24
5:
每季度定时执行
例如每季度的第一天凌晨1点执行
Interval=>TRUNC（ADD_MONTHS（SYSDATE,3）,'Q'）+1/24
6:
每半年定时执行
例如：
每年7月1日和1月1日凌晨1点
Interval=>ADD_MONTHS（trunc（sysdate,'yyyy'）,6）+1/24
7:
每年定时执行
例如：
每年1月1日凌晨1点执行
Interval=>ADD_MONTHS（trunc（sysdate,'yyyy'）,12）+1/24
示例：
variablejobNonumber;--定义任务号
begin
sys.dbms_job.submit（job=>:
jobNo,--任务号，上面定义的变量，系统会自动生成一个INT类型的数字
what=>'P_USER_TABLES_SYNC;',--任务执行的对象，这里是存储过程P_USER_TABLES_SYNC
next_date=>to_date（'19-07-200610:
49:
50','dd-mm-yyyyhh24:
mi:
ss'）,--定义执行的时间
interval=>'sysdate+2/1440'）;--定义执行的间隔时间，定义为2分钟执行一次，24小时＊60分钟＝1440分钟
commit;
end;
variablejobNonumber;--定义任务号
begin
dbms_job.submit（:
jobNo,'proc_;',sysdate,'sysdate+1/1440'）;--每天1440分钟，一分钟运行proc_过程一次
commit;
end;
2.2查看作业
select*fromuser_jobs;
dba_jobs，all_jobs，user_jobs，dba_jobs_running（包含正在运行job相关信息）
注意：
当执行job出现错误时，Oracle将其记录在日志里，失败次数每次自动加1。
当执行失败次数达到16时，Oracle就将该job标志为broken。
此后，Oracle不再继续执行它，直到用户调用过程dbms_job.broken，重新设置为notbroken，或强制调用dbms_job.run来重新执行它。
DBA_JOBS和USER_JOBS.字典视图的字段含义
字段（列）类型描述
JOBNUMBER任务的唯一标示号
LOG_USERVARCHAR2（30）提交任务的用户
PRIV_USERVARCHAR2（30）赋予任务权限的用户
SCHEMA_USERVARCHAR2（30）对任务作语法分析的用户模式
LAST_DATEDATE最后一次成功运行任务的时间
LAST_SECVARCHAR2（8）如HH24:
MM:
SS格式的last_date日期的小时，分钟和秒
HIS_DATEDATE正在运行任务的开始时间，如果没有运行任务则为null
THIS_SECVARCHAR2（8）如HH24:
MM:
SS格式的this_date日期的小时，分钟和秒
NEXT_DATEDATE下一次定时运行任务的时间
NEXT_SECVARCHAR2（8）如HH24:
MM:
SS格式的next_date日期的小时，分钟和秒
TOTAL_TIMENUMBER该任务运行所需要的总时间，单位为秒
BROKENVARCHAR2
（1）标志参数，Y标示任务中断，以后不会运行
INTERVALVARCHAR2（200）用于计算下一运行时间的表达式
FAILURESNUMBER任务运行连续没有成功的次数
WHATVARCHAR2（2000）执行任务的PL/SQL块
CURRENT_SESSION_LABELRAWMLSLABEL该任务的信任Oracle会话符
CLEARANCE_HIRAWMLSLABEL该任务可信任的Oracle最大间隙
CLEARANCE_LORAWMLSLABEL该任务可信任的Oracle最小间隙
NLS_ENVVARCHAR2（2000）任务运行的NLS会话设置
MISC_ENVRAW（32）任务运行的其他一些会话参数
2.3运行作业
begin
dbms_job.run（:
job）;
end;
2.4删除作业
--1selectjobfromuser_jobswherewhat='存储过程名;';
--2sys.dbms_job.remove（JOB）;
3dbms_jobpackage用法介绍
3.1Broken
Broken（）过程更新一个已提交的工作的状态，典型地是用来把一个已破工作标记为未破工作。
这个过程有三个参数：
job、broken与next_date。
PROCEDUREBROKEN
（JOBINBINARY_INTEGER,
BROKENINBOOLEAN,
NEXT_DATEINDATE:
=SYSDATE
）
job参数是工作号，它唯一标识工作。
broken参数指示此工作是否将标记为破——TRUE说明此工作将标记为破，而FLASE说明此工作将标记为未破。
next_date参数指示在什么时候此工作将再次运行。
此参数缺省值为当前日期和时间。
job如果由于某种原因未能成功执行，oracle将重试16次后，还未能成功执行，将被标记为broken，重新启动状态为broken的job，有如下两种方式;
a、利用dbms_job.run（）立即执行该job
sql>begin
sql>dbms_job.run（:
jobno）--该jobno为submit过程提交时返回的jobnumber
sql>end;
sql>/
b、利用dbms_job.broken（）重新将broken标记为false
sql>begin
sql>dbms_job.broken（:
job,false,next_date）
sql>end;
sql>/
3.2Change
Change（）过程用来改变指定工作的设置。
这个过程有四个参数：
job、what、next_date与interval。
PROCEDURECHANGE
（JOBINBINARY_INTEGER,
WHATINVARCHAR2,
NEXT_DATEINDATE,
INTERVALINVARCHAR2
）
此job参数是一个整数值，它唯一标识此工作。
What参数是由此工作运行的一块PL/SQL代码块。
next_date参数指示何时此工作将被执行。
interval参数指示一个工作重执行的频度。
3.3Interval
Interval（）过程用来显式地设置重执行一个工作之间的时间间隔数。
这个过程有两个参数：
job与interval。
PROCEDUREINTERVAL
（JOBINBINARY_INTEGER,
INTERVALINVARCHAR2
）
job参数标识一个特定的工作；interval参数指示一个工作重执行的频度。
3.4ISubmit
ISubmit（）过程用来用特定的工作号提交一个工作。
这个过程有五个参数：
job、what、next_date、interval与no_parse。
PROCEDUREISUBMIT
（JOBINBINARY_INEGER,
WHATINVARCHAR2,
NEXT_DATEINDATE,
INTERVALINVARCHAR2,
NO_PARSEINBOOEAN:
=FALSE
）
这个过程与Submit（）过程的唯一区别在于此job参数作为IN型参数传递且包括一个由开发者提供的工作号；如果提供的工作号已被使用，将产生一个错误。
3.5Next_Date
Next_Date（）过程用来显式地设定一个工作的执行时间。
这个过程接收两个参数：
job与next_date。
PROCEDURENEXT_DATE
（JOBINBINARY_INEGER,
NEXT_DATEINDATE
）
job标识一个已存在的工作；next_date参数指示了此工作应被执行的日期与时间。
3.6Remove
Remove（）过程来删除一个已计划运行的工作。
这个过程接收一个参数：
PROCEDUREREMOVE（JOBINBINARY_INEGER）;
job参数唯一地标识一个工作。
这个参数的值是由为此工作调用Submit（）过程返回的job参数的值；已正在运行的工作不能由调用过程序删除。
3.7Run
Run（）过程用来立即执行一个指定的工作。
这个过程只接收一个参数：
PROCEDURERUN（JOBINBINARY_INEGER）
job参数标识将被立即执行的工作。
3.8Submit
使用Submit（）过程，工作被正常地计划好。
这个过程有五个参数：
job、what、next_date、interval与no_parse。
PROCEDURESUBMIT
（JOBOUTBINARY_INEGER,
WHATINVARCHAR2,
NEXT_DATEINDATE,
INTERVALINVARCHAR2,
NO_PARSEINBOOEAN:
=FALSE
）
job参数是由Submit（）过程返回的binary_ineger；这个值用来唯一标识一个工作。
what参数是将被执行的PL/SQL代码块。
next_date参数指识何时将运行这个工作。
interval参数何时这个工作将被重执行。
no_parse参数指示此工作在提交时或执行时是否应进行语法分析，TRUE指示此PL/SQL代码在它第一次执行时应进行语法分析，而FALSE指示本PL/SQL代码应立即进行语法分析。
3.9User_Export
User_Export（）过程返回一个命令，此命令用来安排一个存在的工作以便此工作能重新提交。
此程序有两个参数：
job与my_call。
PROCEDUREUSER_EXPORT
（JOBINBINARY_INEGER,
MY_CALLINOUTVARCHAR2
）
job参数标识一个安排了的工作。
my_call参数包含在它的当前状态重新提交此工作所需要的正文。
3.10What
What（）过程应许在工作执行时重新设置此正在运行的命令。
这个过程接收两个参数：
job与what。
PROCEDUREWHAT
（JOBINBINARY_INEGER,
WHATINOUTVARCHAR2
）
job参数标识一个存在的工作。
what参数指示将被执行的新的PL/SQL代码。
4实战
升级会员 