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博客

通过oracle dbms_job创建table

最新文章