1、新建一存储过程
create or replace procedure pro_gz_day_report
as
p_sql varchar2(30000); ---处理报表的sql语句
num number;
begin
---先删除数据
execute immediate 'truncate table gz_teacher_day_sms';
execute immediate 'truncate table gz_admin_day_sms';
select count(1) into num from all_tables where Lower(TABLE_NAME) = 'temp_gz_teacher';
if num= 1 then
execute immediate 'drop table temp_gz_teacher';
end if;
select count(1) into num from all_tables where Lower(TABLE_NAME) = 'temp_gz_suc';
if num= 1 then
execute immediate 'drop table temp_gz_suc';
end if;
select count(1) into num from all_tables where Lower(TABLE_NAME) = 'temp_gz_sum';
if num= 1 then
execute immediate 'drop table temp_gz_sum';
end if;
select count(1) into num from all_tables where Lower(TABLE_NAME) = 'temp_gz_13suc';
if num= 1 then
execute immediate 'drop table temp_gz_13suc';
end if;
select count(1) into num from all_tables where Lower(TABLE_NAME) = 'temp_gz_13sum';
if num= 1 then
execute immediate 'drop table temp_gz_13sum';
end if;
select count(1) into num from all_tables where Lower(TABLE_NAME) = 'temp_gz_admin_dxsucc';
if num= 1 then
execute immediate 'drop table temp_gz_admin_dxsucc';
end if;
select count(1) into num from all_tables where Lower(TABLE_NAME) = 'temp_gz_admin_dxsum';
if num= 1 then
execute immediate 'drop table temp_gz_admin_dxsum';
end if;
----教师成功临时表
p_sql := 'create table temp_gz_13suc as (select ....)';
execute immediate p_sql;
commit;
----教师总量临时表
p_sql := 'create table temp_gz_13sum as (select ....)';
execute immediate p_sql;
commit;
----教师表
p_sql := 'create table temp_gz_teacher as (select ....)';
execute immediate p_sql;
commit;
------管理员成功量
p_sql := 'create table temp_gz_admin_succ as (select ....)';
execute immediate p_sql;
commit;
------管理员总量
p_sql := 'create table temp_gz_admin_dxsum as (select ....)';
execute immediate p_sql;
commit;
------统计教师信息量
p_sql := 'insert into gz_teacher_day_sms
select te.county_id,te.county_name,te.ec_code,te.school_name,te.user_name,te.user_mobile,te.class_name,nvl(te.cnt,0),nvl(s.cc,0),nvl(sc.cc,0) from temp_gz_teacher te
inner join temp_gz_sum s on (te.user_id = s.user_id and te.class_id = s.class_id )
inner join temp_gz_suc sc on (s.user_id = sc.user_id and s.class_id = sc.class_id )';
execute immediate p_sql;
commit;
-- 统计管理员信息量
p_sql := 'insert into gz_admin_day_sms
select ss.county_id,ss.county_name,ss.ec_code,ss.school_name,nvl(ss.ss,0),nvl(suc.suc,0) from temp_gz_admin_dxsum ss
left join temp_gz_admin_dxsucc suc on ss.school_id = suc.school_id';
execute immediate p_sql;
commit;
end pro_gz_day_report;
2、问题:
SQL> exec pro_gz_day_report;
ORA-01031: insufficient privileges
ORA-06512: at "....", line 6
ORA-06512: at line 2
3、解决方案:
①、第一种在网上看的说是在存储过程名后加上 Authid Current_User ,加上试过的确exec可以执行,不过用job定时执行一直报错
ORA-12011: 无法执行 1 作业
ORA-06512: 在 "SYS.DBMS_IJOB", line 406
ORA-06512: 在 "SYS.DBMS_JOB", line 275
ORA-06512: 在 line 1
②、第二种当前用户已经是dba角色权限,但是还报权限不足无法创建表,可以通过如下方案解决:grant create any table to 用户名; 授权之后就ok了!
4、创建job
begin
sys.dbms_job.submit(job => :job,
what => 'pro_gz_day_report;',
next_date => to_date('18-10-2012 14:25:55', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'sysdate+1');
commit;
end;
/
5、删除job
begin
dbms_job.remove(203);
end;
6、查看所有job
select * from dba_jobs;
7、查看正在运行的job
select * from dba_jobs_running;
8、停止正在运行的job
①、SQL> select * from dba_jobs_running;//查出正在运行的job
②、SQL> select sid,serial# from v$session where sid='16';
③、SQL> alter system kill session 'sid,serial#';//分别填上②中查出的两个值