7 Replies Latest reply: Feb 10, 2014 10:52 PM by GregV RSS


Vishs Newbie
Currently Being Moderated

Hi All,


my oracle version,


Oracle Database 11g Enterprise Edition Release - 64bit Production

PL/SQL Release - Production

"CORE    Production"

TNS for Linux: Version - Production

NLSRTL Version - 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?

    GregV Guru
    Currently Being Moderated


    Here is what the documentation says about the source_file parameter of the GET_FILE procedure:


    You obtain the value of external_log_id from the ADDITIONAL_INFO column 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_id name/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?

    Vishs Newbie
    Currently Being Moderated

    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.


    log_id: 55465.0000129139516532686426428306481435

    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.

    GregV Guru
    Currently Being Moderated

    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.

    Vishs Newbie
    Currently Being Moderated

    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?

    GregV Guru
    Currently Being Moderated

    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 ?)




    Vishs Newbie
    Currently Being Moderated

    Good hint.


    it is in sys.GV_$SCHEDULER_RUNNING_JOBS table column with JOB_ID which is attached to the file name.


    Thanks for your help.

    GregV Guru
    Currently Being Moderated



    Thanks for the feedback. Good to know where to find the information after all. Can be useful for me later if I need to write external jobs.


  • Correct Answers - 10 points
  • Helpful Answers - 5 points