创建ORACLE JOB和语法

    http://www.cnblogs.com/conqueror/archive/2010/10/10/1847085.html
oracle job简介
主要的使用情景
定时在后台执行相关操作:如每天晚上0点将一张表的数据保存到另一张表中,2：定时备份数据库等
一、
熟化说万事开头难，这里我只简单记述一个创建一个简单的job
步骤如下：
1、创建一张表g_test
create table G_TEST
(
ID     NUMBER(12),
C_DATE DATE
)
2、创建一个sequence
create sequence G_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 141
increment by 1
cache 20;

3、创建一个存储过程
create or replace procedure prc_g_test is
begin
insert into g_test values(g_seq.nextval,sysdate);
end prc_g_test;

4、创建job,
使用Submit()过程，工作被正常地计划好。
这个过程有五个参数：job、what、next_date、interval与no_parse。

PROCEDURE Submit ( job       OUT binary_ineger,
What      IN  varchar2,
next_date IN  date,
interval  IN  varchar2,
no_parse  IN  booean:=FALSE)

job参数是由Submit()过程返回的binary_ineger。这个值用来唯一标识一个工作。
what参数是将被执行的PL/SQL代码块。
next_date参数指识何时将运行这个工作。
interval参数何时这个工作将被重执行。
no_parse参数指示此工作在提交时或执行时是否应进行语法分析——TRUE
指示此PL/SQL代码在它第一次执行时应进行语法分析，
而FALSE指示本PL/SQL代码应立即进行语法分析。

在command window窗口中执行下面脚本
variable job1 number;
begin
sys.dbms_job.submit(job => :job,
what => 'prc_g_test;',
next_date => to_date('22-10-2008 10:06:41', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'sysdate+1/1440');--每天1440分钟，即一分钟运行test过程一次
commit;
end;
/

----------------------------------------------------------------------------------
在plSQL中我的做法是：

declare 
job number;    
begin
sys.dbms_job.submit(job,'prc_g_test;',sysdate,'sysdate+1/1440');
end;
----------------------------------------------------------------------------------

5、查看创建的job
查看相关job信息
1、相关视图
dba_jobs
all_jobs
user_jobs
dba_jobs_running 包含正在运行job相关信息。
如：
select * from dba_jobs

6、运行JOB
说明：Run()过程用来立即执行一个指定的工作。这个过程只接收一个参数：
SQL> begin
2  dbms_job.run(:job);
3  end;
4  /

----------------------------------------------------------------------------------
在plSQL中我的做法是：
begin
dbms_job.run(3017);
end;
----------------------------------------------------------------------------------

7、删除JOB
SQL> begin
2  dbms_job.remove(:job);--:job可以用dba_jobs.job的值代替如：1198
3  end;
4  /


说明：在sqlplus命令行直接定义变量要variable！！！！


二、Oracle Job 语法
初始化相关参数job_queue_processes
alter system set job_queue_processes=39 scope=spfile;//最大值不能超过1000 ;job_queue_interval = 10 //调度作业刷新频率秒为单位

job_queue_process 表示oracle能够并发的job的数量，可以通过语句　　
show parameter job_queue_process;
来查看oracle中job_queue_process的值。当job_queue_process值为0时表示全部停止oracle的job，可以通过语句

ALTER SYSTEM SET job_queue_processes = 10;

来调整启动oracle的job。

相关视图：
dba_jobs
all_jobs
user_jobs
dba_jobs_running 包含正在运行job相关信息

－－－－－－－－－－－－－－－－－－－－－－－－－
提交job语法：

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;
/
－－－－－－－－－－－－－－－－－－－－－－－－－
创建JOB
variable jobno number;
begin
dbms_job.submit(:jobno, 'P_CRED_PLAN;',SYSDATE,'SYSDATE+1/2880',TRUE);
commit;

运行JOB
SQL> begin
         dbms_job.run(:job1);
         end;
         /

删除JOB
SQL> begin
        dbms_job.remove(:job1);
        end;
        /

DBA_JOBS
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
字段（列）          类型                 描述
JOB                NUMBER          任务的唯一标示号
LOG_USER           VARCHAR2(30)    提交任务的用户
PRIV_USER          VARCHAR2(30)    赋予任务权限的用户
SCHEMA_USER        VARCHAR2(30)    对任务作语法分析的用户模式
LAST_DATE          DATE            最后一次成功运行任务的时间
LAST_SEC           VARCHAR2(8)     如HH24:MM:SS格式的last_date日期的小时，分钟和秒
THIS_DATE     DATE            正在运行任务的开始时间，如果没有运行任务则为null
THIS_SEC     VARCHAR2(8)     如HH24:MM:SS格式的this_date日期的小时，分钟和秒
NEXT_DATE          DATE            下一次定时运行任务的时间
NEXT_SEC           VARCHAR2(8)     如HH24:MM:SS格式的next_date日期的小时，分钟和秒
TOTAL_TIME         NUMBER          该任务运行所需要的总时间，单位为秒
BROKEN             VARCHAR2(1)     标志参数，Y标示任务中断，以后不会运行
INTERVAL           VARCHAR2(200)   用于计算下一运行时间的表达式
FAILURES    NUMBER     任务运行连续没有成功的次数
WHAT               VARCHAR2(2000) 执行任务的PL/SQL块
CURRENT_SESSION_LABEL RAW          MLSLABEL 该任务的信任Oracle会话符
CLEARANCE_HI      RAW MLSLABEL     该任务可信任的Oracle最大间隙
CLEARANCE_LO      RAW              MLSLABEL 该任务可信任的Oracle最小间隙
NLS_ENV           VARCHAR2(2000)   任务运行的NLS会话设置
MISC_ENV          RAW(32)          任务运行的其他一些会话参数

－－－－－－－－－－－－－－－－－－－－－－－－－－
描述                    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

8、JOB不运行的检查步骤：
ORACLE有一种定时调度机制，用dbms_job包来管理。
　　设置的JOB就是不运行，搞得的郁闷，
　　最好执行了这个才搞定 exec dbms_ijob.set_enabled(true);
　　下面提供一个checklist用于检查job异常的原因：

　　1) Instance in RESTRICTED SESSIONS mode?

　　Check if the instance is in restricted sessions mode:

　　select instance_name,logins from v$instance;

　　If logins=RESTRICTED, then:

　　alter system disable restricted session;

　　^– Checked!

　　2) JOB_QUEUE_PROCESSES=0

　　Make sure that job_queue_processes is > 0

　　show parameter job_queue_processes

　　^– Checked!

　　3) _SYSTEM_TRIG_ENABLED=FALSE

　　Check if _system_enabled_trigger=false

　col parameter format a25

　　col value format a15

　　select a.ksppinm parameter,b.ksppstvl value from x$ksppi a,x$ksppcv b

　　where a.indx=b.indx and ksppinm=’_system_trig_enabled’;

　　If _system_trig_enabled=false, then

　　alter system set “_system_trig_enabled”=TRUE scope=both;

　　^– Checked!

　　4) Is the job BROKEN?

　　select job,broken from dba_jobs where job=<job_number>;

　　If broken, then check the alert log and trace files to diagnose the issue.

　　^– Checked! The job is not broken.

　　5) Is the job COMMITted?

　　Make sure a commit is issued after submitting the job:

BEGIN

　　SYS.DBMS_JOB.SUBMIT

　　(

　　job => X

　　,what => ‘dbms_utility.analyze_schema

　　(”SCOTT”,”COMPUTE”,NULL,NULL,NULL);’

　　,next_date => to_date(’08/06/2005 09:35:00′,’dd/mm/yyyy hh24:mi:ss’)

　　,no_parse => FALSE

　　);

　　COMMIT;

　　END;

　　/

　　If the job executes fine if forced (i.e., exec dbms_jobs.run(<job_no>);), then likely a commit

　　is missing.

　　^– Checked! The job is committed after submission.

　　6) UPTIME > 497 days

　　Check if the server (machine) has been up for more than 497 days:

　　For SUN, use ‘uptime’ OS command.

　　If uptime>497 and the jobs do not execute automatically, then you are hitting unpublished bug 3427424

　　(Jobs may stop running after 497 days uptime) which is fixed in 9206 and A102

　　^– Checked! The server in this case has been up 126 days only

　　7) DBA_JOBS_RUNNING

　　Check dba_jobs_running to see if the job is still running:

　　select * from dba_jobs_running;

　　^– Checked! The job is not running.

　　LAST_DATE and NEXT_DATE

　　Check if the last_date and next_date for the job are proper:

　　select Job,Next_date,Last_date from dba_jobs where job=<job_number>;

^– NEXT_DATE is porper, however LAST_DATE is null since the job never executes automatically.

9) NEXT_DATE and INTERVAL

　　Check if the Next_date is changing properly as per the interval set in dba_jobs:

　　select Job,Interval,Next_date,Last_date from dba_jobs where job=<job_number>;

　　^– This is not possible since the job never gets executed automatically.

　　10) Toggle value for JOB_QUEUE_PROCESSES

　　Stop and restart CJQ process(es)

　　alter system set job_queue_processes=0 ;

　　–<Wait for some time to ensure CJQ process stopped>

　　alter system set job_queue_processes=4 ;

　　Ref: Bug 2649244 (fixed by: 9015, 9203, 10201)

　　^– Done but did not help

　　11) DBMS_IJOB(Non-documented):

　　Last ditch effort.

　　Either restart the database or try the following:

　　exec dbms_ijob.set_enabled(true);

　　Ref: Bug 3505718 (Closed, Not a Bug)

　　Done but did not help

　　These are the most common causes for this behavior.

　　Solution

　　The solution ended up to be the server (machine) uptime.

　　Even though it was up for only 126 days, after the server was rebooted all jobs were able to execute automatically.

　　To implement the solution, please execute the following steps:

　　1. Shutdown all applications, including databases.

　　2. Shutdown the server (machine)

　　3. Restart all applications, including databases.

　　4. Check that jobs are executing automatically.

　　from metalink docs ： 313102.1