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：参考资料

1. http://www.blogjava.net/pdw2009/archive/2007/02/09/99002.html

2. http://www.cnblogs.com/lumnm/archive/2012/08/31/2666155.html