先查询作业相关视图和参数;
查看调度相关参数和视图:
查询作业;
查询程序计划;
下面参照网上资料,创建一个作业,在emp表中插入数据,运行100次后终止;
不知哪错了,没创建成功;
在sql developer中运行前面语句,结果如下;
启动作业;
但是查看emp表,并未插入数据;不知哪错了;以后有空再搞;
看一下创建的作业是存在的;
sql语句;
--查看调度相关参数和视图:
show parameter JOB_QUEUE_PROCESSES
show parameter RESOURCE_MANAGER_PLAN
set linesize 140 pagesize 30
col state for a15
col job_name for a30
col owner for a15
col job_style for a30
col job_type for a30
--查询作业
select OWNER, JOB_NAME, JOB_STYLE, JOB_TYPE, STATE from DBA_SCHEDULER_JOBS;
col program_name for a30
col program_type for a30
--查询程序计划
select OWNER, PROGRAM_NAME, PROGRAM_TYPE, ENABLED, MAX_RUNS from DBA_SCHEDULER_PROGRAMS;
1. 创建作业、计划和时间表
insert into c##scott.emp values(7944,'testname','clerk',7968,sysdate,2000,null,30);
BEGIN
sys.dbms_scheduler.create_job(
job_name => 'insertemp',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
-- Insert PL/SQL code here
insert into c##scott.emp values (7944,"testname222","clerk",7968,sysdate,2000,null,30);
commit;
end;',
repeat_interval => 'FREQ=SECONDLY',
start_date => to_timestamp_tz('2016-09-01 18:52:00', 'YYYY-MM-DD HH24:MI:SS TZR'),
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'Insert the current date into the emp table.',
auto_drop => FALSE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => 'insertemp', attribute => 'max_runs', value => 10000);
END;
exec dbms_scheduler.enable('insertemp');
exec dbms_scheduler.disable('insertemp');
参考资料
http://www.cnblogs.com/jyzhao/p/5051417.html