5.dbms_job管理_dbms job-优快云博客

Note:The DBMS_JOB package has been superseded by the DBMS_SCHEDULER package, and support for DBMS_JOB might be removed in future releases of Oracle Database. In particular, if you are administering jobs to manage system load, you are encouraged to disable DBMS_JOB by revoking the package execution privilege for users. #注意：DBMS_JOB包已被DBMS_SCHEDULER包取代，在Oracle数据库的未来版本中可能会删除对DBMS_JOB的支持。特别是，如果您正在管理作业以管理系统负载，则建议您通过取消用户的包执行权限来禁用DBMS_JOB

DBMS_JOB Security Model

No specific system privileges are required to use DBMS_JOB. No system privileges are available to manage DBMS_JOB. Jobs cannot be altered or deleted other than jobs owned by the user. This is true for all users including those users granted DBA privileges. #没有特定的系统权限去管理DBMS_JOB。JOB不能被owner以外的用户alter 或者删除，即使拥有DBA权限。

You can execute procedures that are owned by the user or for which the user is explicitly granted EXECUTE. However, procedures for which the user is granted the execute privilege through roles cannot be executed. #可以执行用户拥有的过程，或明确授予用户execute权限的过程。但是，不能执行通过角色授予用户执行权限的过程

Note that, once a job is started and running, there is no easy way to stop the job. #一旦一个作业启动并运行，就没有简单的方法可以停止该作业。

DBMS_JOB Operational Notes

These notes describe stopping a job, and working with Oracle Real Application Clusters. #下面介绍停止一个JOB和在RAC环境创建、管理一个JOb

Stopping a Job

Note that, once a job is started and running, there is no easy way to stop the job. 一旦一个作业启动并运行，就没有简单的方法可以停止该作业。

Working with Oracle Real Application Clusters

DBMS_JOB supports multi-instance execution of jobs. By default jobs can be executed on any instance, but only one single instance will execute the job. In addition, you can force instance binding by binding the job to a particular instance. You implement instance binding by specifying an instance number to the instance affinity parameter. Note, however, that in Oracle Database 10g Release 1 (10.1) instance binding is not recommended. Service affinity is preferred. This concept is implemented in the DBMS_SCHEDULER package. #DBMS_JOB支持多实例执行作业。默认情况下，作业可以在任何实例上执行，但只有一个实例会执行作业。此外，还可以通过将作业绑定到特定实例来强制实例绑定。可以通过为实例关联参数指定实例编号来实现实例绑定。但是，请注意，在Oracle Database 10g Release 1（10.1）中，不建议使用实例绑定。

The following procedures can be used to create, alter or run jobs with instance affinity. Note that not specifying affinity means any instance can run the job. # 以下过程可用于创建、更改或运行具有实例相关性的作业。请注意，不指定相关性意味着任何实例都可以运行作业

DBMS_JOB.SUBMIT #提交
DBMS_JOB.INSTANCE #指定job的运行实例
DBMS_JOB.CHANGE #调整job
DBMS_JOB.RUN #运行JOB

使用示例如下：

1.DBMS_JOB.SUBMIT

To submit a job to the job queue, use the following syntax:

DBMS_JOB.SUBMIT(
job OUT BINARY_INTEGER,
what IN VARCHAR2,
next_date IN DATE DEFAULT SYSDATE,
interval IN VARCHAR2 DEFAULT 'NULL',
no_parse IN BOOLEAN DEFAULT FALSE,
instance IN BINARY_INTEGER DEFAULT ANY_INSTANCE,
force IN BOOLEAN DEFAULT FALSE);

Use the parameters instance and force to control job and instance affinity. The default value of instance is 0 (zero) to indicate that any instance can execute the job. To run the job on a certain instance, specify the instance value. Oracle displays error ORA-23319 if the instance value is a negative number or NULL. #使用参数instance和force来控制作业和实例相关性。实例的默认值为0（零），表示任何实例都可以执行作业。若要在某个实例上运行作业，请指定实例值。如果实例值为负数或NULL，Oracle将显示错误ORA-23319

The force parameter defaults to false. If force is TRUE, any positive integer is acceptable as the job instance. If force is FALSE, the specified instance must be running, or Oracle displays error number ORA-23428. #force参数默认为false。如果force为TRUE，则任何正整数都可以作为作业实例。如果force为FALSE，则指定的实例必须正在运行，或者Oracle显示错误号ORA-23428

2.DBMS_JOB.INSTANCE

To assign a particular instance to execute a job, use the following syntax: #要分配特定实例来执行作业，请使用以下语法：

DBMS_JOB.INSTANCE( JOB IN BINARY_INTEGER,
instance IN BINARY_INTEGER,
force IN BOOLEAN DEFAULT FALSE);

The FORCE parameter in this example defaults to FALSE. If the instance value is 0 (zero), job affinity is altered and any available instance can execute the job despite the value of force. If the INSTANCE value is positive and the FORCE parameter is FALSE, job affinity is altered only if the specified instance is running, or Oracle displays error ORA-23428. #本例中的FORCE参数默认为FALSE。如果实例值为0（零），则会更改作业相关性，并且任何可用实例都可以执行作业，而不管强制值是多少。如果INSTANCE值为正，FORCE参数为FALSE，则只有在指定实例正在运行或Oracle显示错误ORA-23428时，作业相关性才会更改。

If the force parameter is TRUE, any positive integer is acceptable as the job instance and the job affinity is altered. Oracle displays error ORA-23319 if the instance value is negative or NULL. #如果force参数为TRUE，则任何正整数都可以作为作业实例，并且作业相关性会发生更改。如果实例值为负数或NULL，Oracle将显示错误ORA-23319

3.DBMS_JOB.CHANGE

To alter user-definable parameters associated with a job, use the following syntax:

DBMS_JOB.CHANGE( JOB IN BINARY_INTEGER,
what IN VARCHAR2 DEFAULT NULL,
next_date IN DATE DEFAULT NULL,
interval IN VARCHAR2 DEFAULT NULL,
instance IN BINARY_INTEGER DEFAULT NULL,
force IN BOOLEAN DEFAULT FALSE );

Two parameters, instance and force, appear in this example. The default value of instance is null indicating that job affinity will not change. #此示例中显示了两个参数，instance和force。实例的默认值为null，表示作业相关性不会更改

The default value of force is FALSE. Oracle displays error ORA-23428 if the specified instance is not running and error ORA-23319 if the instance number is negative. #force 的默认值为FALSE。如果指定的实例没有运行，Oracle将显示错误ORA-23428，如果实例号为负数，则显示错误ORA-23319。

4.DBMS_JOB.RUN

The force parameter for DBMS_JOB.RUN defaults to FALSE. If force is TRUE, instance affinity is irrelevant for running jobs in the foreground process. If force is FALSE, the job can run in the foreground only in the specified instance. Oracle displays error ORA-23428 if force is FALSE and the connected instance is the incorrect instance.# DBMS_JOB.RUN的force默认为FALSE。如果force为TRUE，则实例相关性与前台进程中运行作业无关。如果force为FALSE，则作业只能在指定实例的前台运行。如果force为FALSE并且连接的实例是不正确的实例，则Oracle显示错误ORA-23428

DBMS_JOB.RUN(
job IN BINARY_INTEGER,
force IN BOOLEAN DEFAULT FALSE);

不想写了，需要用到的时候，查看官网即可。

3 DBMS_JOB和生产应用

dbms_job运行异常排查

1、dbms_job在ORACLE并没有设置独立的系统表来记录运行的过程。

（oracle 推荐使用的DBMS_SCHEDULER 倒是有对应视图：dba_scheduler_job_run_details、dba_scheduler_running_jobs 来记录运行过程）

2、不过，job最终还是调用what里面的存储过程，存储过程还是过程、SQL的执行，执行报错还是会记录到警报日志。所以DBMS_JOB可以通过以下方式追溯定时任务执行异常的原因：

1.通过dba_errors --据说是有报错抛出，但是还没验证过

2.通过警报日志，查看异常时段对应的日志。

通过警报日志追溯问题的例子：

可以直接查找定时任务调用的存储过程名(找到对应的错误日志)：

grep -i procedure_name alert_sid.log

ORA-12012: 自动执行作业 1082 出错
ORA-04068: 已丢弃程序包的当前状态
ORA-04065: 未执行, 已变更或删除 stored procedure "xxx"
ORA-06508: PL/SQL: 无法找到正在调用 : "xx" 的程序单元
ORA-06512: 在 "xx", line 162
ORA-06512: 在 line 1

通过内容分析错误的原因:

经过排查确认，本次原因为存储过程内容调用的对象发生了DDL变更，导致缓存元数据失效，进而触发存储过程执行异常，定时任务异常。

上次两种办法没法定位问题，那孩子也无力回天了。