oracle -- 授权 SCHEDULER

--授权 SCHEDULER和JOB
GRANT SCHEDULER_ADMIN TO username;

GRANT CREATE JOB TO scott;

GRANT ALTER myjob1 TO scott;

GRANT MANAGE SCHEDULER TO adam;


Grant SELECT_CATALOG_ROLE to username;
GRANT SCHEDULER_ADMIN TO username; 
grant create job to username;
grant create PROCEDURE to username;
grant CREATE TRIGGER to username;
grant CREATE View to username;
grant EXECUTE ANY PROCEDURE to username;
grant MANAGE SCHEDULER to username;


-- 开始一个JOB
BEGIN

DBMS_SCHEDULER.CREATE_JOB (

   job_name            =>  'TEST_JOB',

   job_type            =>  'STORED_PROCEDURE',    --job的类型是执行sql语句

   job_action          =>  'PROC_TEST',

   start_date          =>   sysdate,

   repeat_interval     =>  'freq = minutely; interval=1',   --每分钟执行一次

   enabled             =>   true,

   comments            =>  'MY JOB');

END;

/

-- 运行JOB

exec dbms_scheduler.enable ('TEST_JOB');

-- 删除JOB
BEGIN

DBMS_SCHEDULER.DROP_JOB (           --删除job；多个job间用逗号隔开

   job_name   =>  'TEST_JOB',

   force      =>  TRUE);

END;




----------------------------------------------------
-- 创建调度，每隔1个小时调度一次
BEGIN

DBMS_SCHEDULER.CREATE_SCHEDULE (           --创建计划任务

  schedule_name     => 'KK_SCHEDULE',

  repeat_interval   => 'FREQ=HOURLY; INTERVAL=1',    --执行间隔：每1小时

  comments          => 'Every 1 HOURS');

END;

/

-- 创建job 并把它加入到scheduler里面
BEGIN

DBMS_SCHEDULER.CREATE_JOB (      

   job_name                 =>  'JOB_CLLCX',

   job_type                 =>  'STORED_PROCEDURE',

   job_action               =>  'PROC_CLLTJ',

   schedule_name            =>  'KK_SCHEDULE');

END;

/


--  查询JOB
select job_name, schedule_name from user_scheduler_jobs;

-- JOB是否运行
SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'JOB_CLLCX';

-- 运行JOB
BEGIN

DBMS_SCHEDULER.ENABLE ('JOB_CLLCX');   /* sys.jobclass1下的所有jobs都会被enable */

END;

--DBMS_SCHEDULER 运行信息
select job_name,state,enabled,to_char(last_start_date,'yyyy-mm-dd hh24:mi:ss'), schedule_name
from dba_scheduler_jobs;
 
--DBMS_SCHEDULER运行成功与否信息
SELECT log_id, job_name, status,TO_CHAR(ACTUAL_START_DATE,'yyyy-mm-dd HH24:MI:ss') start_date,
           TO_CHAR (log_date, 'yyyy-mm-dd HH24:MI:ss') log_date
      FROM dba_scheduler_job_run_details
     WHERE job_name = 'GATHER_STATS_JOB'
     order by 4 DESC;
 
--查询执行时间情况
select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
 where t1.window_name=t2.window_name and t2.window_group_name='MAINTENANCE_WINDOW_GROUP';
 
--修改执行时间
begin
dbms_scheduler.set_attribute('WEEKEND_WINDOW','REPEAT_INTERVAL','freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0');
dbms_scheduler.set_attribute('WEEKEND_WINDOW','DURATION','+002 00:00:00');
end;