--登录sys用户,创建一个测试用户,权限可以给大点
SQL> create user test identified by xxxxx;
SQL> grant connect to test;
SQL> grant resource to test;
SQL> grant dba to test;
--登录test用户,开始测试--建个测试表create table test_table(id int, time timestamp);
--创建Jobbegin
dbms_scheduler.create_job(
job_name => 'test_job',
job_type => 'PLSQL_BLOCK',
job_action => 'insert into test_table
(select
nvl(max(id),0)+1, systimestamp from test_table, dual);',
--nvl函数同SQLServer的isnull函数 start_date => null,
--一激活,就开始 repeat_interval => '
FREQ=SECONDLY;INTERVAL=10');
end;
FREQ用来指定间隔的时间周期,可选参数有:YEARLY,MONTHLY,WEEKLY,DAILY,HOURLY,MINUTELY,SECONDLY。--光创建没用,还需要激活;也可以在创建时,直接把enable属性设置为true(enabled => true)select job_name, enabled, run_count from user_scheduler_jobs;
JOB_NAME ENABL RUN_COUNT
------------------------------ ----- ----------
TEST_JOB FALSE 0begin
dbms_scheduler.enable('test_job');
end;
--查看效果select id, to_char(time,'HH24:MI:SS:FF3') from test_table;
ID TO_CHAR(TIME,'HH24
---------- ------------------
1 16:13:29:542
2 16:13:39:506
3 16:13:49:109
4 16:13:59:097
5 16:14:09:109
6 16:14:19:103
7 16:14:29:101
8 16:14:39:099
9 16:14:49:105
10 16:14:59:100--停止任务begin
dbms_scheduler.disable('test_job');
end;
--删除任务begin
dbms_scheduler.drop_job('test_job');
end;
posted on 2009-12-17 16:13
Jcat 阅读(238)
评论(0) 编辑 收藏 所属分类:
Database