Forum Stats

  • 3,874,365 Users
  • 2,266,735 Discussions
  • 7,911,832 Comments

Discussions

Job does not complete

655497
655497 Member Posts: 43
edited Sep 25, 2008 2:39PM in Scheduler
I created a job using Oracle stored procedure. When I run the procedure manually it completes well. But when I run it using the configured job, it never completes after 18 hours. I need to restart database instance and kill it.

Is there any way to find out what the job was doing all the time? Are there any log files. Please let me know, I'm new to Oracle jobs.
Tagged:

Answers

  • 295223
    295223 Member Posts: 3
    Can you show how the job is being creating? It sounds like the job was created successfully, but not enabled. If that's the case, it wouldn't run at all.

    When you create the job, check out it's STATE:
    *select state from dba_scheduler_jobs where job_name='[THEJOB]';*

    If it's 'DISABLED', try running:
    *EXEC dbms_scheduler.enable ( name => '[THEJOB]' );*
  • Rnr-Oracle
    Rnr-Oracle Member Posts: 1,269 Employee
    Hi,

    Do a select from dba_scheduler_jobs, what state is the job in ?

    If the job is disabled, then you have forgotten to enable the job (use dbms_scheduler.enable() ).

    If the job is listed as running then there should be a row in dba_scheduler_running_jobs.

    If the job is in a running state you can use the session ID from dba_scheduler_running_jobs to query v$session to query what sql statement the job is running.

    There are only log entries in dba_scheduler_job_run_details when a job has finished running. Before a job has finished running you should use v$session to see what the job is doing.

    Hope this helps,
    Ravi.
    Rnr-Oracle
  • 655497
    655497 Member Posts: 43
    v$session doesn't give what query is being executed.
  • Rnr-Oracle
    Rnr-Oracle Member Posts: 1,269 Employee
    Hi,

    It does not show it directly, but you can use it with the v$sqlarea view to find the SQL text. See here for more info

    http://www.dbasupport.com/oracle/ora10g/watching0101.shtml
    http://www.dbasupport.com/oracle/ora10g/watching0102.shtml

    The query should look like
    select sesion.sid,
    sesion.username,
    optimizer_mode,
    hash_value,
    address,
    cpu_time,
    elapsed_time,
    sql_text
    from v$sqlarea sqlarea, v$session sesion
    where sesion.sql_hash_value = sqlarea.hash_value
    and sesion.sql_address = sqlarea.address
    and sesion.username is not null
    Hope this helps,
    Ravi.
This discussion has been closed.