数据库创建的JOB通常是在调用的对象如存储过程中编写SQL insert记录日志表来跟踪运行结果，但是同样需要考虑一个问题就是写日志的事务提交是否影响了存储过程中的逻辑结构，甚至我喜欢在PROCEDURE中增加一些DBMS_OUTPUT输出调试过程，但是只有在控制台运行才可以看到输出， 在11g及之前的版本中使用DBMS_SCHEDLER创建的JOB已经增加了DBA_SCHEDULER_JOB_RUN_DETAILS 视图可以记录一些运行的日志信息如error#，在12C的版本中再增强，同样记录了procedure中使用的DBMS_OUTPUT的输出和ERROR的具体文本， 这里记录一个简单的例子.
[oracle@anbob ~]$ sqlplus anbob/anbob
SQL*Plus: Release 12.2.0.0.0 Beta on Thu Sep 29 14:22:44 2016
Copyright (c) 1982, 2015, Oracle. All rights reserved.
Last Successful login time: Thu Sep 29 2016 14:20:55 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.1 - 64bit Beta
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
anbob@pdborcl:orcl> show con_name
CON_NAME
------------------------------
PDBORCL
anbob@pdborcl:orcl> create or replace procedure p_do is
2 begin
3 dbms_output.put_line('step 1');
4 dbms_lock.sleep(5);
5 dbms_output.put_line('step 2');
6 dbms_lock.sleep(5);
7 dbms_output.put_line('step 3');
8 end;
9 /
Procedure created.
anbob@pdborcl:orcl> set serveroutput on
anbob@pdborcl:orcl> exec p_do;
step 1
step 2
step 3
PL/SQL procedure successfully completed.
anbob@pdborcl:orcl> begin
2 dbms_scheduler.create_job (
3 job_name => 'pdo',
4 job_type => 'STORED_PROCEDURE',
5 job_action => 'p_do',
6 start_date => sysdate,
7 enabled => true,
8 comments => 'test output in proc');
9 end;
10 /
PL/SQL procedure successfully completed.
Note:
上面创建了简单的存储过程使用DBMS_OUTPUT输出几行记录，并创建了一次性的JOB。oracle 创建只运行一次的job 自动删除
sys@cdb$root:orcl> @desc DBA_SCHEDULER_JOB_RUN_DETAILS
Name Null? Type
------------------------------- -------- ----------------------------
1 LOG_ID NOT NULL NUMBER
2 LOG_DATE TIMESTAMP(6) WITH TIME ZONE
3 OWNER VARCHAR2(128)
4 JOB_NAME VARCHAR2(261)
5 JOB_SUBNAME VARCHAR2(261)
6 STATUS VARCHAR2(30)
7 ERROR# NUMBER
8 REQ_START_DATE TIMESTAMP(6) WITH TIME ZONE
9 ACTUAL_START_DATE TIMESTAMP(6) WITH TIME ZONE
10 RUN_DURATION INTERVAL DAY(3) TO SECOND(0)
11 INSTANCE_ID NUMBER
12 SESSION_ID VARCHAR2(128)
13 SLAVE_PID VARCHAR2(30)
14 CPU_USED INTERVAL DAY(3) TO SECOND(2)
15 CREDENTIAL_OWNER VARCHAR2(261)
16 CREDENTIAL_NAME VARCHAR2(261)
17 DESTINATION_OWNER VARCHAR2(261)
18 DESTINATION VARCHAR2(261)
19 ADDITIONAL_INFO VARCHAR2(4000)
20 ERRORS VARCHAR2(4000)
21 OUTPUT VARCHAR2(4000)
22 BINARY_ERRORS BLOB
23 BINARY_OUTPUT BLOB
# 11g release 2
SQL> @desc DBA_SCHEDULER_JOB_RUN_DETAILS
Name Null? Type
------------------------------- -------- ----------------------------
1 LOG_ID NUMBER
2 LOG_DATE TIMESTAMP(6) WITH TIME ZONE
3 OWNER VARCHAR2(30)
4 JOB_NAME VARCHAR2(65)
5 JOB_SUBNAME VARCHAR2(65)
6 STATUS VARCHAR2(30)
7 ERROR# NUMBER
8 REQ_START_DATE TIMESTAMP(6) WITH TIME ZONE
9 ACTUAL_START_DATE TIMESTAMP(6) WITH TIME ZONE
10 RUN_DURATION INTERVAL DAY(3) TO SECOND(0)
11 INSTANCE_ID NUMBER
12 SESSION_ID VARCHAR2(30)
13 SLAVE_PID VARCHAR2(30)
14 CPU_USED INTERVAL DAY(3) TO SECOND(2)
15 CREDENTIAL_OWNER VARCHAR2(65)
16 CREDENTIAL_NAME VARCHAR2(65)
17 DESTINATION_OWNER VARCHAR2(128)
18 DESTINATION VARCHAR2(128)
19 ADDITIONAL_INFO VARCHAR2(4000)
NOTE:
12C DBA_SCHEDULER_JOB_RUN_DETAILS的视图中比11G 增加了4列，2种信息，OUTPUT 和ERROR文本，和V$SQL视图一样，同样一种是VARCHAR2 4000的列，和对应的一列BLOB列。
anbob@pdborcl:orcl> COL OUTPUT FOR A30
anbob@pdborcl:orcl> COL OWNER FOR A30
anbob@pdborcl:orcl> select OWNER,JOB_NAME,OUTPUT
2 from DBA_SCHEDULER_JOB_RUN_DETAILS
3 where job_name = 'PDO';
OWNER JOB_NAME OUTPUT
------------------------------ -------------------- ------------------------------
ANBOB PDO step 1
step 2
step 3
anbob@pdborcl:orcl> col outblob for a100
anbob@pdborcl:orcl>select utl_raw.cast_to_varchar2( dbms_lob.substr( BINARY_OUTPUT, 32000, 1 ) ) outblob,BINARY_OUTPUT
2 from DBA_SCHEDULER_JOB_RUN_DETAILS
3* where job_name = 'PDO'
anbob@pdborcl:orcl> /
OUTBLOB BINARY_OUTPUT
------------------------------ ----------------------------------------------------------------------------------------------------
step 1 7374657020310A7374657020320A737465702033
step 2
step 3
step 1 7374657020310A7374657020320A737465702033
step 2
step 3
NOTE:
对于OUTPUT和ERROR文本超过4000会自动截断，从BINARY_OUTPUT列取出全文本。 ERROR不再演示。