Oracle job 管理
上一篇 / 下一篇 2009-03-01 17:12:34 / 个人分类:Oracle
- 文件版本: V1.0
- 开发商: 本站原创
- 文件来源: 本地
- 界面语言: 简体中文
- 授权方式: 免费
- 运行平台: Win9X/Win2000/WinXP
初始化相关参数job_queue_processes
9N0qa(d8rfu0alter system set job_queue_processes=39 scope=spfile;//最大值不能超过1000 ;job_queue_interval = 10 //调度作业刷新频率秒为单位51Testing软件测试网)|]Y9BNK p
DBA_JOBS describes all jobs in the database.
,GbY9o!` Q5n0USER_JOBS describes all jobs owned by the current user
1 select job,what,to_char(last_date,'yyyy-mm-dd HH24:mi:ss'),to_char(next_date,'yyyy-mm-dd HH24:m),interval from dba_jobs where job in (325,295)
8r'm;b$w&J02 select job,what,last_date,next_date,interval from dba_jobs where job in (1,3);51Testing软件测试网8W{c:Xo%z$x
8ke[%q YQW(k&|0
p_Xktz0查询job的情况。51Testing软件测试网@3{C+l0Emv LH
show paramter background_dump_dest.
4q0b$g!nV:YCS+w0看alter.log 和trace
"{2X#Bi'dHT0SVRMGR> select * from dba_jobs;51Testing软件测试网.\2MsC xpL"Ta
51Testing软件测试网 u3p.DrL?初始化相关参数job_queue_processes
B/J%M9g%]T0alter system set job_queue_processes=39 scope=spfile;//最大值不能超过100051Testing软件测试网2m%s8VhUy0i
job_queue_interval = 10 //调度作业刷新频率秒为单位
v5C7]Ac8wn!|09[K-y2}y051Testing软件测试网'Lwq^Dm
DBA_JOBS describes all jobs in the database.51Testing软件测试网c\Q,mn
USER_JOBS describes all jobs owned by the current user51Testing软件测试网&A)A[9@^q'A:l+g\!u
4gUq'Pc:bd.z01 select job,what,to_char(last_date,'yyyy-mm-dd HH24:mi:ss'),to_char(next_date,'yyyy-mm-dd HH24:m),interval from dba_jobs where job in (325,295)51Testing软件测试网(h2N8?3Y%L
2 select job,what,last_date,next_date,interval from dba_jobs where job in (1,3);51Testing软件测试网J7E.y4T0s$j#B
s3e-`)wE1q9H n|0
}7u-^z+I8{0查询job的情况。51Testing软件测试网kJ/l.~!C*\6ZK\
show paramter background_dump_dest.
/[%t?*UJM0看alter.log 和trace51Testing软件测试网cwnM ^{7j
请问我如何停止一个JOB51Testing软件测试网4|"b%Jt@3^'|2c%|
SQL> exec dbms_job.broken(1,true)51Testing软件测试网meR(aTn7^8I.k f
/qU| B(H q4a/~WU\0PL/SQL 过程已成功完成。51Testing软件测试网 HjH*{(Z
p
SQL>commit //必须提交否则无效51Testing软件测试网8^a:Dz9z%v;r
#a$j{&yb{U#x&~0启动作业51Testing软件测试网Z+rwC!xm
SQL> exec dbms_job.broken(1,false)
I3s y(N2znAy0PL/SQL 过程已成功完成。51Testing软件测试网H,q9Ch\E4L
?%b&F0dk lIE*V%[0停其他用户的job
gG^ V8?1kYl1Qx8`0SQL>exec sys.dbms_ijob.broken(98,true);51Testing软件测试网cj7["N6P2_4f
SQL>commit;51Testing软件测试网N#Lft/sUz:t
aR`z-I_jH051Testing软件测试网5UW(P l/A
============================51Testing软件测试网N?Z6K+C8Di+]yR
exec dbms_job.broken(:job) 停止
}d1jrEX(J,R0exec dbms_job.broken(186,true) //标记位broken51Testing软件测试网-H@+oq.F,^q&B
exec dbms_job.broken(186,false)//标记为非broken51Testing软件测试网5vY#_:AAG0Pzi
exec dbms_job.broken(186,false,next_day(sysdate,'monday')) //标记为非broken,指定执行时间
7D5Y)r9VRf0exec dbms_job.remove(:job);删除
JGpV4G,_#po0exec dbms_job.remove(186);51Testing软件测试网)FFyLf8[{
commit;
把一个broken job重新运行51Testing软件测试网0KI rR|2w
']}8Gw#J051Testing软件测试网c@ Z/a3Dq oQ
三、查看相关job信息
rCq@.F.n8m01、相关视图
E[u.v7x0dba_jobs
o R4k
S6} ^ER-H0all_jobs51Testing软件测试网P"d]k\H?D
user_jobs51Testing软件测试网5Z8rN4d;V[%B.K
dba_jobs_running 包含正在运行job相关信息51Testing软件测试网k[3{6w$FeU
+S"o4?
nA5f0创建JOB
^ _,d"i6?"H[$d0variable jobno number;
+ovp^C&xV-`0begin
(z'nB@}0dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
u*m3H:q;Xf}#?0commit;51Testing软件测试网4^G
l%Guc
end;
wa:yV|
]%l1w0print jobno51Testing软件测试网4Q9v#_A
e.j8`N$^1y
&V
V1cY-k8HX0例如,我们已经建立了一个存储过程,其名称为my_job,在sql/plus中以scott用户身份登录,执行如下命令:51Testing软件测试网;^g"Kr;{s7[au\
sql> variable n number;51Testing软件测试网#z#x ~W6o
sql> begin
\FZ7K6Uv b*Q0dbms_job.submit(:n‘my_job;’,sysdate,
y;`g"A.p-W0‘sysdate+1/360’);51Testing软件测试网a~-N+AX#`
`L
commit;51Testing软件测试网6Lw
d:\M.a&[
end;51Testing软件测试网*\|$I;NC
Sql> print :n;51Testing软件测试网4G2Y7X5o
|)I6J2w:k
系统提示执行成功。
*}GC-Of0Sql> print :n;
{q5n4B5yi f(w0系统打印此任务的编号,例如结果为300。51Testing软件测试网em-W1ovu&sw#U3Y}3R
\D(AT~7ft0简单例子
,dl,Dj-_:zI'jx)Ho0一个简单例子:
$g!v }$K6\3w0sd0创建测试表51Testing软件测试网0svU9gL-f
SQL> create table TEST(a date);51Testing软件测试网dn2xA/?*Dd
7nXxc-B6cNbb;zY0表已创建。
#\ufVBW0K!OC#p'hst0bR,n0创建一个自定义过程51Testing软件测试网2QSzL'I6St^
SQL> create or replace procedure MYPROC as
~!|o&_\wqy02 begin
2XB+b.\#gMiiiq03 insert into TEST values(sysdate);
%z;{2N0Xz-s{?04 end;
A.Cw'^7c`t05 /51Testing软件测试网w)g%Bbv
^#Q6T)F
\
-_TZ{ y%iF&XP0过程已创建。51Testing软件测试网1g+x5_fK!H iN
51Testing软件测试网:e^R q _d创建JOB
q!f-V9JL0SQL> variable job1 number;51Testing软件测试网9r$bm`.M8E
SQL>
b4y^g!nX5R$F:C0SQL> begin
5R)W UxWZc02 dbms_job.submit(:job1,'MYPROC;',sysdate,'sysdate+1/1440'); --每天1440分钟,即一分钟运行test过程一次51Testing软件测试网U9wVHq7PUla
3 end;
(x G"OI`*m'Zh6l2R04 /
PL/SQL 过程已成功完成。
:@&A.h [PP^051Testing软件测试网9S c*eo4JB8d+f运行JOB
)J$Q5Y!Z#e0SQL> begin51Testing软件测试网G+^4N+Q.rX
2 dbms_job.run(:job1);51Testing软件测试网 v)NX(I8@|$v
3 end;51Testing软件测试网:Pf:m7W4YG
4 /51Testing软件测试网*C]/Z `.dx4e5H5?
PL/SQL 过程已成功完成。51Testing软件测试网s8t!C[c$B$R
51Testing软件测试网7NjW1k~g%kp%BSQL> select to_char(a,'yyyy/mm/dd hh24:mi:ss') 时间 from TEST;51Testing软件测试网'J'T!\-pif!RU p6w
UPv+wq [M0时间
*O)O~,xZ5H/PiAY0-------------------
`G6[%TJ-h d02001/01/07 23:51:2151Testing软件测试网 {u;aMZ!d:Iy(z
2001/01/07 23:52:22
2R9z3m*JXG]5x02001/01/07 23:53:24
(C@[pF0]$cCE0删除JOB51Testing软件测试网;Cts~%Q,S
SQL> begin
&LA"@X G.k*|7R02 dbms_job.remove(:job1);51Testing软件测试网gc#?$Js
3 end;51Testing软件测试网|7a*_]E
4 /
PL/SQL 过程已成功完成。
H/g9X#e V{&IT k02aj;Z5l8{Y]\9N,u0=======================================51Testing软件测试网rq9U@0a']:Mz g
51Testing软件测试网!jQQCN1G
l9Uz5|^7v0a、利用dbms_job.run()立即执行该job51Testing软件测试网TI7p@Q,E7D/uP
sql>begin
? w%o
h'fpZYjZ0sql>dbms_job.run(:jobno) 该jobno为submit过程提交时返回的job number
P qQoqqH;I0sql>end;
M|f
O{&i