Capture an error trace from a failed scheduler job?
I had to solve a problem with executing CREATE INDEX on a table inside a scheduler job. The job FAILED, but I could only get one error code out of USER_SCHEDULER_JOB_RUN_DETAILS. I had to run the CREATE INDEX manually (that is, not in a job) to get the complete list of errors. What Iâd like to know is, is there a way to capture a complete error trace in a scheduler job?
Here is the situation (âthe names are changed to protect the innocentâ). We are using Oracle 10g. Assume that a table called DOCUMENTS has been created. Assume that Oracle Text preferences have been created with the names DOCUMENTS_DATASTORE and DOCUMENTS_STORAGE. Also assume that an Oracle Text section group has been created with the name DOCUMENTS_SECTIONS.
I create and run the job with this statement (normally, the statement is machine-generated):
BEGIN DBMS_SCHEDULER.CREATE_JOB (JOB_NAME => 'DOCUMENTS_INDEX_JOB', JOB_TYPE => 'PLSQL_BLOCK', JOB_ACTION => 'BEGIN EXECUTE IMMEDIATE ''CREATE INDEX DOCUMENTS_INDEX ON DOCUMENTS (DUMMYCOL) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS (''''DATASTORE DOCUMENTS_DATASTORE STORAGE DOCUMENTS_STORAGE SECTION GROUP DOCUMENTS_SECTIONS SYNC (ON COMMIT)'''')'';END;'); DBMS_SCHEDULER.ENABLE ('DOCUMENTS_INDEX_JOB'); END;
The job would run for 1 minute 25 seconds, then stop with the reason failed. Querying the ADDITIONAL_INFO column of USER_SCHEDULER_JOB_RUN_DETAILS for that job yielded a single error message:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
However, if I ran the CREATE INDEX command directly, I got a trace of six error messages:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in dreii0fsh
ORA-01653: unable to extend table SYSTEM.DR$DOCUMENTS_INDEX$I by 128 in tablespace DOCUMENTS_TABLESPACE
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364.
Now, the problem is obvious: the tablespace is exhausted.
I would like to be able to capture this whole error trace from a failed scheduler job to diagnose future errors more quickly. It would be okay to change the machine-generated PL/SQL block, and it would be okay to log the error trace in a user table. Do you have any suggestions?