存储过程中建表无权限ORA-01031: insufficient privileges

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#';//分别填上②中查出的两个值