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

DBMS_SCHEDULER, EXTERNAL_LOG_ID

Vishs Newbie
Currently Being Moderated

Hi All,

 

my oracle version,

 

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

"CORE    11.2.0.3.0    Production"

TNS for Linux: Version 11.2.0.3.0 - Production

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

  • 1. Re: DBMS_SCHEDULER, EXTERNAL_LOG_ID
    GregV Guru
    Currently Being Moderated

    Hi,

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

    http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sched.htm#ARPLS72375

    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?

  • 2. Re: DBMS_SCHEDULER, EXTERNAL_LOG_ID
    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.

  • 3. Re: DBMS_SCHEDULER, EXTERNAL_LOG_ID
    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.

  • 4. Re: DBMS_SCHEDULER, EXTERNAL_LOG_ID
    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?

  • 5. Re: DBMS_SCHEDULER, EXTERNAL_LOG_ID
    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 ?)

    - sys.SCHEDULER$_EVENT_LOG

    - sys.SCHEDULER$_JOB_DESTINATIONS

    - sys.SCHEDULER$_JOB_RUN_DETAILS

  • 6. Re: DBMS_SCHEDULER, EXTERNAL_LOG_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.

  • 7. Re: DBMS_SCHEDULER, EXTERNAL_LOG_ID
    GregV Guru
    Currently Being Moderated

    Hi,

     

    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.

Legend

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