在SQLPLUS中写: This submits a new job to the job queue. The job calls the procedure DBMS_DDL.ANALYZE_OBJECT to generate optimizer statistics for the table DQUON.ACCOUNTS. The statistics are based on a sample of half the rows of the ACCOUNTS table. The job is run every 24 hours: VARIABLE jobno number; BEGIN DBMS_JOB.SUBMIT(:jobno, 'dbms_ddl.analyze_object(''TABLE'', ''DQUON'', ''ACCOUNTS'', ''ESTIMATE'', NULL, 50);' SYSDATE, 'SYSDATE + 1'); commit; END; / Statement processed. print jobno JOBNO ---------- 14144
一个简单例子: 创建测试表 SQL> create table a(a date);表已创建。创建一个自定义过程 SQL> create or replace procedure test as 2 begin 3 insert into a values(sysdate); 4 end; 5 /过程已创建。创建JOB SQL> variable job1 number; SQL> SQL> begin 2 dbms_job.submit(:job1,'test;',sysdate,'sysdate+1/1440'); --每天1440分钟,即一分钟运行test过程一次 3 end; 4 /PL/SQL 过程已成功完成。运行JOB SQL> begin 2 dbms_job.run(:job1); 3 end; 4 /PL/SQL 过程已成功完成。SQL> select to_char(a,'yyyy/mm/dd hh24:mi:ss') 时间 from a;时间 ------------------- 2001/01/07 23:51:21 2001/01/07 23:52:22 2001/01/07 23:53:24删除JOB SQL> begin 2 dbms_job.remove(:job1); 3 end; 4 /PL/SQL 过程已成功完成。
小弟对ORACLE不是很熟悉,您能否说的详细一点?
写一过程
写上
insert into A2 select * from A1;
This submits a new job to the job queue. The job calls the procedure DBMS_DDL.ANALYZE_OBJECT to generate optimizer statistics for the table DQUON.ACCOUNTS. The statistics are based on a sample of half the rows of the ACCOUNTS table. The job is run every 24 hours: VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'dbms_ddl.analyze_object(''TABLE'',
''DQUON'', ''ACCOUNTS'',
''ESTIMATE'', NULL, 50);'
SYSDATE, 'SYSDATE + 1');
commit;
END;
/
Statement processed.
print jobno
JOBNO
----------
14144
SQL> create table a(a date);表已创建。创建一个自定义过程
SQL> create or replace procedure test as
2 begin
3 insert into a values(sysdate);
4 end;
5 /过程已创建。创建JOB
SQL> variable job1 number;
SQL>
SQL> begin
2 dbms_job.submit(:job1,'test;',sysdate,'sysdate+1/1440'); --每天1440分钟,即一分钟运行test过程一次
3 end;
4 /PL/SQL 过程已成功完成。运行JOB
SQL> begin
2 dbms_job.run(:job1);
3 end;
4 /PL/SQL 过程已成功完成。SQL> select to_char(a,'yyyy/mm/dd hh24:mi:ss') 时间 from a;时间
-------------------
2001/01/07 23:51:21
2001/01/07 23:52:22
2001/01/07 23:53:24删除JOB
SQL> begin
2 dbms_job.remove(:job1);
3 end;
4 /PL/SQL 过程已成功完成。