Oracle job定时创建备份表

--プロシージャを新規する
create or replace procedure createTblG202T
Authid Current_User
as
  tabname varchar(200);
begin
  select 'G202T_' || to_char(sysdate, 'yyyymmdd') into tabname from dual;
  execute immediate 'create table ' || tabname ||' tablespace gau12d as select * from ;
  commit;
end;
/

--実行テスト
begin
 createTblG202T;
end;

--Create new Job
VARIABLE jobno number;

begin
dbms_job.submit(JOB=>:jobno,
            WHAT=>'createTblG202T;',
            NEXT_DATE=>SYSDATE+5/60/24,
            INTERVAL=>'TRUNC(SYSDATE+1)+18/24'
            );
commit;
end;
/

--显示地赋予权限：

grant create table to dev;

--手动执行JOB测试

BEGIN
  DBMS_JOB.RUN(23);
END;

--查看JOB信息

select * from user_jobs;
select * from dba_jobs;
select * from dba_jobs_running

手順：
--Oracle通过job定时创建表

--Managing Job Queues

 

問題纏め：

You might review all those database objects you are trying to access from that procedure in the job.
Sometimes, Oracle expects you to grant explicit privileges on database objects rather than through roles.
So try granting explicit select/update/delete (or whatever) privileges and rerun the job

 

--job prucedure ORA-01031: 权限不足 错误诊断
http://blog.csdn.net/liuya1985liuya/archive/2008/11/12/3285476.aspx
--ORA-12012+ORA-01031