一.每天统计用户表数量,字段总数,数据量
CREATE OR REPLACE PROCEDURE P_SJZXTJB AUTHID CURRENT_USER AS
BEGIN
update SJZXTJB set SJZXTJB.XTBSL= (select count(*) from dba_tables t where t.owner='XT') where XTMC='公交行业基础设施管理系统(XT)';
update SJZXTJB set SJZXTJB.XTZDZS= (select count(*) from dba_col_comments WHERE OWNER='XT') where XTMC='公交行业基础设施管理系统(XT)';
update SJZXTJB set SJZXTJB.XTSJL= (select sum(NUM_ROWS) from (select NUM_ROWS,TABLE_NAME from dba_all_tables WHERE OWNER='XT')) where XTMC='公交行业基础设施管理系统(XT)';
commit;
END P_SJZXTJB;
存储过程报错:dba_tables,dba_col_comments , dba_all_tables,表不存在;
此时需要,显示通过sys用户授权
grant select any table to SJZXTJ(用户);
grant select any dictionary to SJZXTJ(用户);
这样,编译通过,可以执行静态SQL
二.创建JOB
Declare
i Integer;
Begin
dbms_job.submit(i,'P_SJZXTJB;',Sysdate,'sysdate+1/1440');
end;
--查询JOB
select * from user_jobs;
三.启动JOB
Declare
job_num Integer;
Begin
-- 查找计划号
Select t.JOB Into job_num From User_Jobs t ;
-- 运行制定的执行计划
dbms_job.run(job_num);
end;