oracle创建job后消失,oracle 创建只运行一次的job 自动删除

有时会有这样的需求，自己写个procedure，想下班后自动执行，但又只想运行一次，然后只关心job的结果，如果用dbms_job

可能就要建个job,然后频率间隔很长时间，执行完后在把那个job drop,有没有一种job执行一次后自动删除呢？有

利用dbms_scheduler package，早就用它来替换dbms_job,下面我用它来实现上面的需求

语法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);

这个函数有几种重载，

【job_name】

This attribute specifies the name of the job and

uniquely identifies the job. The name has to be unique in the SQL namespace. For

example, a job cannot have the same name as a table in a schema. If the job

being created will reside in another schema, it must be qualified with the

schema name.

【job_type】

•’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.

【job_action】

This

attribute specifies the action of the job.

For a stored procedure, the action

is the name of the stored procedure. You have to specify the schema if the

procedure resides in another schema than the job.

…

【repeat_interval】

This attribute specifies how often the job should

repeat. You can specify the repeat interval by using calendaring or PL/SQL

expressions.

The expression specified is evaluated to determine the next time

the job should run. If repeat_interval is not specified, the job will run only

once at the specified start date

【end_date】

This attribute specifies the date after which the job will

expire and will no longer be executed. When end_date is reached, the job is

disabled. The STATE of the job will be set to COMPLETED, and the enabled flag

will be set to FALSE.

If no value for end_date is specified, the job will repeat forever unless

max_runs or max_failures is set, in which case the job stops when either value

is reached.

The value for end_date must be after the value for start_date. If

it is not, an error is generated when the job is enabled.

【enabled】

This attribute specifies whether the job is created enabled or

not. The possible settings are TRUE or FALSE. By default, this attribute is set

to FALSE and, therefore, the job is created as disabled. A disabled job means

that the metadata about the job has been captured and the job exists as a

database object but the Scheduler will ignore it and the job coordinator will

not pick the job for processing. In order for the job coordinator to process the

job, the job has to be enabled. You can enable a job by setting this argument to

TRUE or by using the ENABLE procedure.

【auto_drop】

This flag, if TRUE, causes a job to be automatically dropped

after it has completed or has been disabled. A job is considered completed

if:

•Its end date (or its schedule’s end date) has passed

•It has run

max_runs number of times. max_runs must be set with SET_ATTRIBUTE.

•It is not

a repeating job and has run once

A job is disabled when it has failed

max_failures times. max_failures is also set with SET_ATTRIBUTE.

If this flag

is set to FALSE, the jobs are not dropped and their metadata is kept until the

job is explicitly dropped with the DROP_JOB procedure.

By default, jobs are

created with auto_drop set to TRUE.

上面官方文档说的已经很细了，再强调一遍，默认创建的如果未指定enabled=true是disable的,repeat_interval

参数如果未指定是只运行一次的，auto_drop 自动删除是未指定执行频率并且已执行一次的。

我建了三个procedure

qa_bj_02,qa_bj_03,qa_bj_04BEGIN

DBMS_SCHEDULER.CREATE_JOB (

job_name => 'qa_03',

job_type => 'STORED_PROCEDURE',

job_action => ' qa_bj_03 ',

enable =>true,

start_date => trunc(sysdate)+20/24;

);

END;

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

job_name => 'qa_04',

job_type => 'STORED_PROCEDURE',

job_action => ' qa_bj_04 ',

enable =>true,

start_date => trunc(sysdate+1)+5/24;

);

END;

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

job_name => 'qa_02',

job_type => 'STORED_PROCEDURE',

job_action => ' qa_bj_02 ',

enable =>true,

start_date => trunc(sysdate+1)+20/24;

);

END;

创建三个job，10号创建的， 下面是11号的查询的执行情况SQL> l

1* select JOB_NAME,LOG_DATE,STATUS,ERROR#,CPU_USED,ACTUAL_START_DATE from dba_scheduler_job_run_details where job_name like 'QA%'

SQL> /

JOB_NAME LOG_DATE STATUS ERROR# CPU_USED ACTUAL_START_DATE

---------- ------------------------------ --------------- ---------- -------------------- ------------------------------

QA_03 10-JAN-12 08.08.43.005480 PM + SUCCEEDED 0 +000 00:04:49.20 10-JAN-12 08.00.00.096616 PM +

08:00 08:00

QA_04 11-JAN-12 05.35.14.281065 AM + SUCCEEDED 0 +000 00:31:11.61 11-JAN-12 05.00.00.093203 AM +

08:00 08:00

SQL> l

1* SELECT JOB_NAME,JOB_TYPE ,job_action,START_DATE,REPEAT_INTERVAL,state,enabled FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME LIKE 'QA%'

SQL> /

JOB_NAME JOB_TYPE JOB_ACTION START_DATE REPEAT_INTERVAL STATE ENABL

---------- ---------------- -------------------- -------------------------------------- ------------------------------ --------------- -----

QA_02 STORED_PROCEDURE icme.qa_bj_02 11-JAN-12 08.00.00.000000 PM +08:00 SCHEDULED TRUE

从dba_scheduler_job_run_details视图已可以看到qa_03,qa_04已执行成功错误编号无，且从DBA_SCHEDULER_JOBS可以看到已执行的job已经自动删除。