Oracle 基础知识作业的使用

对于DBA来说，数据库Job再熟悉不过了，因为经常要数据库定时的自动执行一些脚本，或做数据库备份，或做数据的提炼，或做数据库的性能优化，包括重建索引等等的工作。
Oracle 视图 User_Jobs 是Oracle数据库中的一个视图，包含当前用户拥有的所有系统作业信息，用户可以通过该视图查询用户作业，并获取相关信息，比如作业名称、作业状态、运行状态。
使用Oracle 视图User_Jobs 有一下两种方法：

1.使用select 语句查询user_jobs 视图
select * from user_jobs;
2. 使用dbms_jobs包来管理user_jobs视图

提交作业

dbms_job.submit(job out binary_integer,
what in varcha2,
next_date in date default sysdate,
interval in varchar2 default ‘null’,
no_parse in boolean default false,–是否需要解析与job相关的过程
instance in binary_integer default 0,–指定那个实例可以运行job
force in boolean default false);–是否前置运行与job相关的实例

二 建立job的方式
在plsql 中使用写脚本的方式建立job，也可以通过plsql 建立
Begin，
dbms_scheduler.create_job(job_name => ‘WORK_FLOW’,
job_type => ‘PLSQL_BLOCK’,
job_action=> ‘FICOINTERFACE.PRO_WORK_FLOW(to_char(trunc(sysdate,’‘mm’‘)-1,’‘yyyymm’‘));’,
start_date => trunc(sysdate) + 0.5,
repeat_interval => ‘FREQ =MONTHLY;BYMONTHDAY=8’,
enabled => true,
auto_drop => false,
comments => ‘调度作业’);
end;
常用参数
job_name 作业名称
job_type 作业类型
job_action 作业执行的操作 执行存储过程
‘FICOINTERFACE.PRO_WORK_FLOW(to_char(trunc(sysdate,’‘mm’‘)-1,’‘yyyymm’'))
start_date 开始时间
repeat_interval 每次重复时间 按月执行 每个月的8号
MONTHLY;BYMONTHDAY=8
Enabled :true

三 创建一个job
示例
1.首先创建一个表，用于存储数据

create table dbms_job_history(message varchar2(100),create_date date);

2.创建一个存储过程

create or replace procedure p_dbms_job_test as
begin
insert into dbms_job_history(message,create_date) values(‘dbms_job’,sysdate);
commit;
end;

2.每三分钟调用一次过程p_dbms_job_test

declare
job_out binary_integer;
begin
dbms_job.submit(
job=>job_out,
what=>‘p_dbms_job_test();’,
next_date=>sysdate, --立即执行
interval=>‘sysdate+1/1440’); --每分钟执行一次
commit;
end;

Interval设置方法

每天午夜12点 trunc(sysdate+1)
每天早上8点30分 trunc(sysdate+1)+(860+30)/(2460)
每星期二中午12点 next_day(trunc(sysdate),'tusday')+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,"staturday"),next_day(sysdate,"sunday")))+(660+10)/2460
每秒钟执行一次 inteval=>sysdate+1/(246060)

例如：每天凌晨1点执行 interval=>trunc(sysdate)+1+1/(24)

暂停

procedure broken(job in binary_integer,broken in boolean,next_date in date default sysdate);

示例

begin
dbms_job.broken(job=>26,broken=>true);
commit;
end;

修改

procedure change(job in binary_integer,
what in varchar2,
next_date in date,
interval in varchar2,
instance in binary_integer default null,
force in boolean default false);

示例

begin
dbms_job.interval(job=>26,interval=>‘sysdate,3/1440’);
commit;
end;

运行

procedure run(job in binary_integer,force in boolean default false);

示例

begin
dbms_job.run(job=>26);
commit;
end;

删除

procedure remove(job in binary_integer,force in boolean default false);

批量删除

begin
for v in (select job from user_jobs where log_user=‘&user’) loop
dbms_job.remove(v.job);
end loop;
commit;
end;

示例

begin
dbms_job.remove(job=>26);
commit;
end;

Interval

1.每分钟执行
Interval=>Trunc(sysdate,‘mi’)+1/(2460)
2.每天凌晨1点
Interval=>Trunc(sysdate)+1+1/24)
3.每天固定时间运行,比如早上8:10
interval=>trunc(sysdate+1)+(860+10)/24*60

停止一个作业
我自己停止job 使用 杀掉会话的方式
如果需要停掉进程 需要停掉该会话：
第一步：查看正在运行的jobs
SQL>select * from dba_jobs_running;

然后确定你要停止的job，这个数据字典对应的job就是下面要执行的过程的第一个参数。

第二步：确定要停掉的job的SID，SERIAL#和系统进程id
select b.SID,b.SERIAL#,c.SPID
from dba_jobs_running a,v$sessionb,v$process c
where a.sid = b.sid and b.PADDR = c.ADDR

第三步：调用dbms_job的broken函数将要停止的job干掉
SQL>  EXEC DBMS_JOB.BROKEN(18,TRUE);

PL/SQL procedure successfully completed

SQL>commit;

此时这个job还是运行的，可以通过dba_jobs_running查看

第四步：使用alter system kill session 'SID,SERIAL#;杀掉会话。

如果要杀很久，直接使用操作系统命令
kill -9 spid
第五步：调整这个job

第六步：将这个job置为正常状态
SQL> EXEC DBMS_JOB.BROKEN(18,FALSE); 
commit;
此时如果直接提交了的话，可能就会马上执行这个job，如果不想让其今天再执行了，可以这样处理，先将这个job remove然后在创建一个新的job
例如
SQL> exec dbms_job.remove(18)
PL/SQL procedure successfully completed
SQL> commit;
再创建一个新的
SQL> declare v_job number;
  2  begin
  3  dbms_job.submit(v_job,‘pro_bak_job;’,to_date(‘2012-11-17 02:00:00’,‘yyyy-mm-dd hh24-mi-ss’),‘TRUNC(SYSDATE+1)+2/24’,false);
  4  commit;
  5  end;
  6  /