通过oracle dbms_job创建table

1,如何利用dbms_job创建表(table)
 conn test/system
2,创建存储过程（创建表）
SQL> create procedure lv
  2  is
  3  i varchar2(1000);
  4  begin
  5  i:='create table bb(a int)';
  6  execute immediate i;
  7  end;
  8  /

Procedure created.
3,测试上述存储过程功能是否正常
SQL> execute lv;--提示如下错误，以sysdba授权就可
BEGIN lv; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TEST.LV", line 6
ORA-06512: at line 1


SQL> conn /as sysdba
Connected.
SQL> grant create any table to test;

SQL>Grant succeeded.
SQL>grant execute on test.lv to test;

SQL> conn test/system
Connected.
SQL> execute lv;

PL/SQL procedure successfully completed.


4,根据dbms_job构建建表job
1  begin
  2  dbms_job.submit(:job1,'lv;',sysdate,'sysdate+1/1440');--lv为引上创建的存储过程
  3* end;
SQL> r
  1  begin
  2  dbms_job.submit(:job1,'lv;',sysdate,'sysdate+1/1440');
  3* end;

PL/SQL procedure successfully completed.

select job from user_jobs;--查出上述创建job

5,手工测试job是否功能正常
conn test/system
exec dbms_job.run(201);  --手工运行
desc bb;--查看表是否存在

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-621098/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-621098/