一点一滴努力,满仓满谷收成

因两阶段提交的问题，偶而会产生prepare事务，通过查询dba_2pc_pending视图可以查到，处理不及时会引发中间件队列异常。
人为的发现会有一定的延后性，如下以一个小例子使这一部分工作自动化，同时也简单的描述一下scheduler job的常见操作，希望能起到三生万物的效果。

1 准备一个处理prepare事务的存储过程，建在sys用户下。

CREATE OR REPLACE PROCEDURE P_DEAL_2PC_PREPARE IS
CURSOR_NAME PLS_INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
IGNORE PLS_INTEGER;

PROCEDURE EXECUTE_IMMEDIATE(P_SQL IN VARCHAR2) IS
BEGIN
DBMS_SQL.PARSE(CURSOR_NAME, P_SQL, DBMS_SQL.NATIVE);
IGNORE := DBMS_SQL.EXECUTE(CURSOR_NAME);
DBMS_SQL.CLOSE_CURSOR(CURSOR_NAME);
END;

BEGIN
FOR X IN (SELECT LOCAL_TRAN_ID
FROM DBA_2PC_PENDING
WHERE STATE NOT LIKE ‘%forced%’) LOOP
DBMS_OUTPUT.PUT_LINE(‘My variable is ‘ || X.LOCAL_TRAN_ID);
EXECUTE_IMMEDIATE(‘rollback force ”’ || X.LOCAL_TRAN_ID || ””);
DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(X.LOCAL_TRAN_ID);
END LOOP;
END P_DEAL_2PC_PREPARE;

2 写一SCHEDULER JOB,同样放在sys用户下。

BEGIN
DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => ‘QIUYB_DEAL_2PC_PREPARE’,
JOB_TYPE => ‘STORED_PROCEDURE’,
JOB_ACTION => ‘P_DEAL_2PC_PREPARE’,
START_DATE => SYSDATE,
REPEAT_INTERVAL => ‘FREQ=MINUTELY;INTERVAL=5′,
COMMENTS=>’rollback force prepared 2pc pending transaction.’);
END;

参数，分别代表的含义如下：
•JOB_NAME ：指定任务的名称，必选值。
•JOB_TYPE ：任务执行的操作类型，必选值，有下列几个可选值：
◦PLSQL_BLOCK ：表示任务执行的是一个PL/SQL匿名块。
◦STORED_PROCEDURE ：表示任务执行的是Oracle过程(含PL/SQL PROCEDURE和JAVA PROCEDURE)，本例中正是指定这一参数值。
◦EXECUTABLE ：表示任务执行的是一个外部程序，比如说操作系统命令。
◦CHAIN ：表示任务执行的是一个CHAIN。
•JOB_ACTION ：任务执行的操作，必选值，应与JOB_TYPE类型中指定的参数相匹配。比如说对于PL/SQL匿名块，此处就可以放置PL/SQL块的具体代表，类似DECLARE .. BEGIN ..END这类；如果是ORACLE过程，那么此处应该指定具体的过程名，注意由于任务执行，即使过程中有OUT之类参数，实际执行时也不会有输出的。
•START_DATE ：指定任务初次执行的时间，本参数可为空，当为空时，表示任务立刻执行，效果等同于指定该参数值为SYSDATE。
•REPEAT_INTERVAL ：指定任务执行的频率，比如多长时间会被触发再次执行。本参数也可以为空，如果为空的话，就表示当前设定的任务只执行一次。REPEAT_INTERVAL参数需要好好说说，REPEAT_INTERVAL参数的语法结构要复杂的多。其中最重要的是FREQ和INTERVAL两个关键字。
◦FREQ 关键字用来指定间隔的时间周期，可选参数有：YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, and SECONDLY，分别表示年、月、周、日、时、分、秒等单位。
◦INTERVAL 关键字用来指定间隔的频繁，可指定的值的范围从1-99。
例如：REPEAT_INTERVAL=>’FREQ=MINUTELY;INTERVAL=5′;表示每5分钟执行一次。
除了上述参数，还有如下可选参数:
•NUMBER_OF_ARGUMENTS ：指定该JOB执行时需要附带的参数的数量，默认值为0，注意当JOB_TYPE列值为PLSQL_BLOCK或CHAIN时，本参数必须设置为0，因为上述两种情况下不支持附带参数。
•END_DATE ：指定任务的过期时间，默认值为NULL。任务过期后，任务的STATE将自动被修改为COMPLETED，ENABLED被置为FALSE。如果该参数设置为空的话，表示该任务永不过期，将一直按照REPEAT_INTERVAL参数设置的周期重复执行，直到达到设置的MAX_RUNS或MAX_FAILURES值。
•JOB_CLASS ：指定任务关联的CLASS，默认值为DEFAULT_JOB_CLASS。
•ENABLED ：指定任务是否启用，默认值为FALSE。FALSE状态表示该任务并不会被执行，除非被用户手动调用，或者用户将该任务的状态修改为TRUE。
•AUTO_DROP ：当该标志被置为TRUE时，ORACLE会在满足条件时自动删除创建的任务
◦任务已过期；
◦任务最大运行次数已达MAX_RUNS的设置值；
◦任务未指定REPEAT_INTERVAL参数，仅运行一次；
•COMMENTS ：设置任务的注释信息，默认值为NULL。
3 修改JOB。
细心的你可能发现了上面JOB虽然成功创建了，但却并未执行，其实原因很简单，CREATE_JOB过程时提到的ENABLED默认值为false，JOB自然不会运行了，可做如下修改。
启用JOB:

exec dbms_scheduler.enable(‘QIUYB_DEAL_2PC_PREPARE’);

禁用JOB:

exec dbms_scheduler.disable(‘QIUYB_DEAL_2PC_PREPARE’);

执行JOB:

exec dbms_scheduler.run_job(‘QIUYB_DEAL_2PC_PREPARE’);

停止JOB:

exec dbms_scheduler.stop_job(‘QIUYB_DEAL_2PC_PREPARE’);

删除JOB:

exec dbms_scheduler.drop_job(‘QIUYB_DEAL_2PC_PREPARE’);

4 为JOB设定属性。
举例：

exec dbms_scheduler.set_attribute(‘QIUYB_DEAL_2PC_PREPARE’,’start_date’,to_date(‘2015-02-14′,’yyyy-mm-dd’));

如下列举几个SET_ATTRIBUTE常用到可修改的属性。
•LOGGING_LEVEL ：指定对jobs执行情况记录的日志信息级别。
SCHEDULER 管理的JOB对任务的执行情况专门进行了记录，同时用户还可以选择日志中记录信息的级别，有下列三种选择：
◦DBMS_SCHEDULER.LOGGING_OFF ：关闭日志记录功能；
◦DBMS_SCHEDULER.LOGGING_RUNS ：对任务的运行信息进行记录；
◦DBMS_SCHEDULER.LOGGING_FULL ：记录任务所有相关信息，不仅有任务的运行情况，甚至连任务的创建、修改等也均将记入日志。
•RESTARTABLE ：指定jobs运行出错后，是否能够适时重启。创建任务时如未明确指定，本参数默认情况下设置为FALSE，如果设置为TRUE，就表示当任务运行时出错，下次运行时间点到达时仍会启动，并且如果运行仍然出错，会继续重新运行，不过如果连接出错达到6次，该job就会停止。
•MAX_FAILURES ：指定jobs最大连续出错次数。该参数值可指定的范围从1-1000000，默认情况下该参数设置为NULL，表示无限制。达到指定出错次数后，该job会被自动disable。
•MAX_RUNS ：指定jobs最大运行次数。该参数值可指定的范围从1-1000000，默认情况下该参数设置为NULL，表示无限制(只是运行次数无限制，实际job会否继续运行，仍受制于end_date以及max_failures等参数的设置)。达到指定运行次数后，该job也将被自动disable，并且状态会被置为COMPLETED。
•JOB_TYPE ：指定job执行的任务的类型。有四个可选值：¨PLSQL_BLOCK¨, ¨STORED_PROCEDURE¨, ¨EXECUTABLE¨, and ¨CHAIN¨。
•JOB_ACTION ：指定job执行的任务。这一参数所指定的值依赖于JOB_TYPE参数中的值，比如说JOB_TYPE设置为¨STORED_PROCEDURE¨，那么本参数值中指定的一定是ORACLE中的过程名。
•START_DATE ：指定job初次启动的时间
•END_DATE ：指定job停止运行的时间。本参数又与AUTO_DROP相关联，如果AUTO_DROP设置为TRUE的话，那么一旦job到达停止运行的时间，该job就会被自动删除，否则的话job一直存在，不过状态被修改为COMPLETED。
上述提到了这些参数，均可以使用DBMS_SCHEDULER.SET_ATTRIBUTE过程进行设置。另外需要注意一点，除了用户手动创建的jobs之外，数据库在运行过程中也有可能自动创建jobs。对于这类jobs除非必要，否则不建议进行修改。至于如何区分jobs是用户创建，还是数据库自动创建，可以通过*_SCHEDULER_JOBS视图的SYSTEM列来确定，如果该列显示为TRUE，则表示由系统创建。
5 相关视图
<1>查看SCHEDULER管理的JOB:
USER_SCHEDULER_JOB_RUN_DETAILS和DBA_SCHEDULER_JOB_RUN_DETAILS两个视图
<2>查看JOB运行状态：
USER_SCHEDULER_JOB_LOG和DBA_SCHEDULER_JOB_LOG两个视图
6 其它
批量生成脚本:

SELECT ‘exec dbms_transaction.purge_lost_db_entry(‘||””||local_tran_id||””||’);commit;’ FROM dba_2pc_pending where state like ‘%force%’;