my oracle version,
Oracle Database 11g Enterprise Edition Release 220.127.116.11.0 - 64bit Production
PL/SQL Release 18.104.22.168.0 - Production
"CORE 22.214.171.124.0 Production"
TNS for Linux: Version 126.96.36.199.0 - Production
NLSRTL Version 188.8.131.52.0 - Production
I am trying to fetch job std, stderr file from OS level when job is running.
Currently, I am able to do this once the job is completed using 'DBA_SCHEDULER_JOB_RUN_DETAILS.additional_info' column. However, I want to fetch details when the job is running but not able to see any column which matches this information 'DBA_SCHEDULER_RUNNING_JOBS' so that I can get the file name to pass it to DBMS_SCHEDULER.get_file procedure.
The format of file name is something like 'job_251177_55065_stderr', I can get job id i.e. 55065 but can't figure out what is 251177 in the file name.any input would be appreciated. or is there a direct way to get file name created by Oracle?
Here is what the documentation says about the source_file parameter of the GET_FILE procedure:
You obtain the value of
ADDITIONAL_INFOcolumn of the
*_SCHEDULER_JOB_RUN_DETAILSviews. This column contains a set of name/value pairs in an indeterminate order, so you must parse this column for the
external_log_idname/value pair, and then append either "
_stdout" or "
_stderr" to its value.
It doesn't really say what the set of value really means as to external_log_id, exactly what is means by "indeterminate" order.
You said you checked the DBA_SCHEDULER_RUNNING_JOBS, but did you chek the last column of this view LOG_ID? As far as I remember, while the job is running, this column contains a decimal value, which surprised me as I couldn't find any reason for that (the documentation doesn't say), but when the job completes I can see that the truncated value is matching the LOG_ID value of the DBA_SCHEDULER_JOB_LOG view. So I'm wondering if this decimal value of LOG_ID in DBA_SCHEDULER_RUNNING_JOBS could in fact match both the values in your file name? Can you check?
Thanks for replying.
I did check log_id column in DBA_SCHEDULER_RUNNING_JOBS table. This is how I get one part of file name. However, I double checked the numbers after decimal place, and they does not match up. e.g.
file name: job_251340_55465_stderr
Also, one more point I want to bring up that these files are created the very moment job is started but the information is only available after the job is completed.
As a solution, I was thinking to list the file names at the OS level using pl/sql to identify the exact file name. However,I would like to use the simple approach or any other simple option to do the same.
Any inputs would be helpful.
Thanks for checking. Indeed that decimal bit is no help.
I suppose the reason you want to get the file's name while the job is running is because you need to store it somewhere, in a log table or something. Getting it from the DBA_SCHEDULER_JOB_RUN_DETAILS once the job completes could be automatic though, with an event-based job. But it still means you'd get it once the job completes.
I wonder if we can dig into the sys scheduler related tables to get that pair of numbers.
Thanks, I am trying to create an interface (using APEX) which provide user to submit a job and track the logs when it is running or completed. Currently, I am able to view the logs once the job is completed, however, I would like to see the logs when the job is running.
so the only approach I have is to find the full file name using pl/sql and pass the name to the get_file procedure. I was trying to search for a simple way.
when you say sys schedule tables, which tables are you referring to?
You said that the file gets created the very moment the job starts, so there must a place where this info is stored to put it into DBA_SCHEDULER_JOB_RUN_DETAILS once the job completes.
About the sys scheduler tables, I was thinking of the scheduler$ sys tables. So can you check the following tables when the job is running:
- sys.GV_$SCHEDULER_RUNNING_JOBS (maybe the other bit is the process_id ?)