DBMS_SCHEDULER常规操作

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:DBMS_SCHEDULER常规操作

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

1.create job

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
  job_name =>'xifenfei_job',
  job_type =>'STORED_PROCEDURE',
  job_action =>'p_schedule',
  repeat_interval =>'FREQ=DAILY; BYHOUR=18,20,22',
  enabled => true,
  comments => 'XIFENFEI');
END;  
--每天18/20/22点执行p_schedule过程

2.disable job

BEGIN
    DBMS_SCHEDULER.DISABLE(name => 'xifenfei_job'); 
END;

3.enable job

BEGIN
    DBMS_SCHEDULER.ENABLE(name => 'xifenfei_job'); 
END;

4.select job

select * from USER_SCHEDULER_JOBS;

5.query logs

SELECT * FROM USER_SCHEDULER_JOB_RUN_DETAILS 
WHERE job_name='XIFENFEI_JOB';

6.delete logs

--ALL_SCHEDULER_JOB_RUN_DETAILS视图
CREATE OR REPLACE VIEW ALL_SCHEDULER_JOB_RUN_DETAILS
(log_id, log_date, owner, job_name, job_subname, status, error#, 
req_start_date, actual_start_date, run_duration, instance_id, 
session_id, slave_pid, cpu_used, credential_owner, credential_name, 
destination_owner, destination, additional_info)
AS
(SELECT
     j.LOG_ID, j.LOG_DATE, e.OWNER,
     DECODE(instr(e.NAME,'"'),0, e.NAME,substr(e.NAME,1,instr(e.NAME,'"')-1)),
     DECODE(instr(e.NAME,'"'),0,NULL,substr(e.NAME,instr(e.NAME,'"')+1)),
     e.STATUS, j.ERROR#, j.REQ_START_DATE, j.START_DATE, j.RUN_DURATION,
     j.INSTANCE_ID, j.SESSION_ID, j.SLAVE_PID, j.CPU_USED,
     decode(e.credential, NULL, NULL,
        substr(e.credential, 1, instr(e.credential, '"')-1)),
     decode(e.credential, NULL, NULL,
        substr(e.credential, instr(e.credential, '"')+1,
           length(e.credential) - instr(e.credential, '"'))),
     decode(bitand(e.flags, 1), 0, NULL,
        substr(e.destination, 1, instr(e.destination, '"')-1)),
     decode(bitand(e.flags, 1), 0, e.destination,
        substr(e.destination, instr(e.destination, '"')+1,
           length(e.destination) - instr(e.destination, '"'))),
     j.ADDITIONAL_INFO
   FROM scheduler$_job_run_details j, scheduler$_event_log e
   WHERE j.log_id = e.log_id
   AND e.type# = 66 and e.dbid is null
   AND ( e.owner = SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
         or  /* user has object privileges */
            ( select jo.obj# from obj$ jo, user$ ju where
                DECODE(instr(e.NAME,'"'),0, e.NAME,substr(e.NAME,1,instr(e.NAME,'"')-1)) 
                = jo.name and e.owner = ju.name and jo.owner# = ju.user#
                and jo.subname is null and jo.type# = 66
            ) in
            ( select oa.obj#
                from sys.objauth$ oa
                where grantee# in ( select kzsrorol from x$kzsro )
            )
         or /* user has system privileges */
            (exists ( select null from v$enabledprivs
                       where priv_number = -265 /* CREATE ANY JOB */
                   )
             and e.owner!='SYS')
        )
  );
--从这个视图中可以发现,日志有存在SCHEDULER$_JOB_RUN_DETAILS和
--SCHEDULER$_EVENT_LOG两张表中,所以要删除日志,就需要处理这两张表

######################删除日志操作#################
--删除SYS.SCHEDULER$_JOB_RUN_DETAILS中数据
DELETE FROM SYS.SCHEDULER$_JOB_RUN_DETAILS A
 WHERE EXISTS (SELECT 1
          FROM SYS.SCHEDULER$_EVENT_LOG B
         WHERE B.NAME = 'XIFENFEI_JOB'
           AND A.LOG_ID = B.LOG_ID);  
--删除SYS.SCHEDULER$_EVENT_LOG中数据     
DELETE FROM SYS.SCHEDULER$_EVENT_LOG B
 WHERE B.NAME = 'XIFENFEI_JOB';
--提交
 COMMIT;
#####################################################

7.delete jobs

BEGIN
    DBMS_SCHEDULER.DROP_JOB(job_name => 'xifenfei_job'); 
END;

参考:http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sched.htm

此条目发表在 Oracle 分类目录。将固定链接加入收藏夹。

DBMS_SCHEDULER常规操作》有 2 条评论

  1. 惜 分飞 说:
    Creates a job in a single call without using an existing program or schedule:
    
    DBMS_SCHEDULER.CREATE_JOB (
       job_name             IN VARCHAR2,
       job_type             IN VARCHAR2,
       job_action           IN VARCHAR2,
       number_of_arguments  IN PLS_INTEGER              DEFAULT 0,
       start_date           IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
       repeat_interval      IN VARCHAR2                 DEFAULT NULL,
       end_date             IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
       job_class            IN VARCHAR2                 DEFAULT 'DEFAULT_JOB_CLASS',
       enabled              IN BOOLEAN                  DEFAULT FALSE,
       auto_drop            IN BOOLEAN                  DEFAULT TRUE,
       comments             IN VARCHAR2                 DEFAULT NULL);
    Creates a job using a named schedule object and a named program object:
    
    DBMS_SCHEDULER.CREATE_JOB (
       job_name                IN VARCHAR2,
       program_name            IN VARCHAR2,
       schedule_name           IN VARCHAR2,
       job_class               IN VARCHAR2              DEFAULT 'DEFAULT_JOB_CLASS',
       enabled                 IN BOOLEAN               DEFAULT FALSE,
       auto_drop               IN BOOLEAN               DEFAULT TRUE,
       comments                IN VARCHAR2              DEFAULT NULL);
    Creates a job using a named program object and an inlined schedule:
    
    DBMS_SCHEDULER.CREATE_JOB (
       job_name             IN VARCHAR2,
       program_name         IN VARCHAR2,
       start_date           IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
       repeat_interval      IN VARCHAR2                 DEFAULT NULL,
       end_date             IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
       job_class            IN VARCHAR2                 DEFAULT 'DEFAULT_JOB_CLASS',
       enabled              IN BOOLEAN                  DEFAULT FALSE,
       auto_drop            IN BOOLEAN                  DEFAULT TRUE,
       comments             IN VARCHAR2                 DEFAULT NULL);
    Creates a job using a named schedule object and an inlined program:
    
    DBMS_SCHEDULER.CREATE_JOB (
       job_name                IN VARCHAR2,
       schedule_name           IN VARCHAR2,
       job_type                IN VARCHAR2,
       job_action              IN VARCHAR2,
       number_of_arguments     IN PLS_INTEGER       DEFAULT 0,
       job_class               IN VARCHAR2          DEFAULT 'DEFAULT_JOB_CLASS',
       enabled                 IN BOOLEAN           DEFAULT FALSE,
       auto_drop               IN BOOLEAN           DEFAULT TRUE,
       comments                IN VARCHAR2          DEFAULT NULL);
    Creates a job using an inlined program and an event:
    
    DBMS_SCHEDULER.CREATE_JOB (
       job_name                IN VARCHAR2,
       job_type                IN VARCHAR2,
       job_action              IN VARCHAR2,
       number_of_arguments     IN PLS_INTEGER       DEFAULT 0,
       start_date              IN TIMESTAMP WITH TIME ZONE,
       event_condition         IN VARCHAR2,
       event_queue             IN VARCHAR2,
       end_date                IN TIMESTAMP WITH TIME ZONE,
       job_class               IN VARCHAR2          DEFAULT 'DEFAULT_JOB_CLASS',
       enabled                 IN BOOLEAN           DEFAULT FALSE,
       auto_drop               IN BOOLEAN           DEFAULT TRUE,
       comments                IN VARCHAR2          DEFAULT NULL);
    Creates a job using a named program object and an event:
    
    DBMS_SCHEDULER.CREATE_JOB (
       job_name                IN VARCHAR2,
       program_name            IN VARCHAR2,
       start_date              IN TIMESTAMP WITH TIME ZONE,
       event_condition         IN VARCHAR2,
       queue_spec              IN VARCHAR2,
       end_date                IN TIMESTAMP WITH TIME ZONE,
       job_class               IN VARCHAR2          DEFAULT 'DEFAULT_JOB_CLASS',
       enabled                 IN BOOLEAN           DEFAULT FALSE,
       auto_drop               IN BOOLEAN           DEFAULT TRUE,
       comments                IN VARCHAR2          DEFAULT NULL);
    

    job_type
    This attribute specifies the type of job that you are creating. If it is not specified, an error is generated. The supported values are:

    'PLSQL_BLOCK'
    This specifies that the job is an anonymous PL/SQL block. Job or program arguments are not supported when the job or program type is PLSQL_BLOCK. In this case, the number of arguments must be 0.
    
    'STORED_PROCEDURE'
    This specifies that the job is a PL/SQL or Java stored procedure, or an external C subprogram. Only procedures, not functions with return values, are supported.
    
    'EXECUTABLE'
    This specifies that the job is a job external to the database. External jobs are anything that can be executed from the operating system's command line. Anydata arguments are not supported with a job or program type of EXECUTABLE. The job owner must have the CREATE EXTERNAL JOB system privilege before the job can be enabled or run.
    
    'CHAIN'
    This specifies that the job is a chain. Arguments are not supported for a chain, so number_of_arguments must be 0.
    
  2. 惜 分飞 说:

    常用执行频率

    freq=daily;byhour=8,13,18;byminute=0;bysecond=0;bydate=0502,0922
    execution times on 05/02 and 09/22 are 8:00 a.m., 1:00 p.m., and 6:00 p.m.
    
    freq=yearly;bymonth=4;bymonthday=15;byhour=9;byminute=0;bysecond=0
    executes on 04/15/05 9:00:00, 04/15/06 9:00:00, 04/15/07 9:00:00, and so on