oracle如何创建作业,oracle中创建作业（跨表空间）

需求：每月1日执行，从TableSpace为SDE的几张表中读取每个工程的设施数量，存储于TableSpace为SCOTT的T_DIC_MTTYP中

环境：PLSQL

步骤：

1. 建立存储过程(在TableSpace为SCOTT下)

create or replace procedure scott.PRO_InsertFacility is

begin

/*污水管*/

insert into SCOTT.T_MTFACILITY

select t2.proid,extract(year from sysdate),extract(month from sysdate),1,sscount

from (select pjnm,count(pjnm) as sscount from SDE.污水管 group by pjnm) t1

inner join SCOTT.T_PROJECTINFO t2 on t1.pjnm=t2.projectname;

commit;

/*污水检查井*/

insert into SCOTT.T_MTFACILITY

select t2.proid,extract(year from sysdate),extract(month from sysdate),2,sscount

from (select pjnm,count(pjnm) as sscount from SDE.污水检查井 group by pjnm) t1

inner join SCOTT.T_PROJECTINFO t2 on t1.pjnm=t2.projectname;

commit;

/*污水泵*/

insert into SCOTT.T_MTFACILITY

select t2.proid,extract(year from sysdate),extract(month from sysdate),3,sscount

from (select pjnm,count(pjnm) as sscount from SDE.污水泵 group by pjnm) t1

inner join SCOTT.T_PROJECTINFO t2 on t1.pjnm=t2.projectname;

commit;

/*污水排水口*/

insert into SCOTT.T_MTFACILITY

select t2.proid,extract(year from sysdate),extract(month from sysdate),4,sscount

from (select pjnm,count(pjnm) as sscount from SDE.污水排水口 group by pjnm) t1

inner join SCOTT.T_PROJECTINFO t2 on t1.pjnm=t2.projectname;

commit;

/*污水排水口*/

insert into SCOTT.T_MTFACILITY

select t2.proid,extract(year from sysdate),extract(month from sysdate),5,sscount

from (select pjnm,count(pjnm) as sscount from SDE.污水蓄水池 group by pjnm) t1

inner join SCOTT.T_PROJECTINFO t2 on t1.pjnm=t2.projectname;

commit;

end PRO_InsertFacility;

2. 此时进行执行时报错：

Compilation errors for PROCEDURE SCOTT.PRO_INSERTFACILITY

Error: PL/SQL: ORA-00942: 表或视图不存在

Line: 34

Text: from (select pjnm,count(pjnm) as sscount from SDE.污水蓄水池 group by pjnm) t1

Error: PL/SQL: SQL Statement ignored

Line: 32

Text: insert into SCOTT.T_MTFACILITY

说明当前用户没有访问SDE方案中污水蓄水池表的select权限，所以要先赋予权限后再次执行存储过程方能成功。

grant select on SDE.污水蓄水池 to scott

3. 建立oracle作业

declare JOB_InsertFacility number;

begin

dbms_job.submit(JOB_InsertFacility,'SCOTT.PRO_InsertFacility;',TRUNC(LAST_DAY(SYSDATE))+1,'TRUNC(LAST_DAY(SYSDATE) + 1)');

commit;

end;

说明：1)JOB_InsertFacility：此任务在任务队列中的编号

2)'SCOTT.PRO_InsertFacility;'：执行的任务的名称及其输入参数

3)TRUNC(LAST_DAY(SYSDATE))+1：任务执行的时间

4)'TRUNC(LAST_DAY(SYSDATE) + 1)'：任务执行的时间间隔(指上一次执行结束到下一次开始执行的时间间隔，当interval设置为null时，该job执行结束后，就被从队列中删除。假如我们需要该job周期性地执行，则要用'sysdate＋m’表示)

4. 查询作业

select * from sys.user_jobs

5. 执行作业

begin

dbms_job.run(21);

commit;

end;

注意：21是查询出来的作业编号

6. 停止作业

begin

dbms_job.remove(21);

commit;

end;

PS：参考资料