This discussion is archived
9 Replies Latest reply: Nov 27, 2012 1:24 AM by moreajays RSS

DBMS scheduler 3

846231 Newbie
846231 Nov 26, 2012 5:58 PM
Currently Being Moderated
Hi all,

I create a sample dbms jobs and schedule using this tutorial > http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/DBMSScheduler/DBMSScheduler.htm

But my tables are not populated. Where can I find the logs in which to trace where did my process stuck?
Or how do you trace the error why the scheduled job did not run?
But if I run it manually it can populate the tables.


Thanks a lot,
Kinz
  • 1. Re: DBMS scheduler 3
    moreajays Pro
    moreajays Nov 26, 2012 5:42 AM (in response to 846231)
    Currently Being Moderated
    Hi ,

    It could be either permission issue .
    Did u tried executing job using exec dbms_ijob.run(<id>); from the same priv user who owns the object?

    Thanks,
    Ajay More
  • 2. Re: DBMS scheduler 3
    Fran Guru
    Fran Nov 26, 2012 5:48 AM (in response to 846231)
    Currently Being Moderated
    to trace a job:
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:534665300346780552

    your job's trace should called like:
    ora_j*.trc
  • 3. Re: DBMS scheduler 3
    846231 Newbie
    846231 Nov 26, 2012 5:47 AM (in response to moreajays)
    Currently Being Moderated
    Thanks Ajay,

    Where do I get the <id> part? My job name is "DW_ETL_JOB"
  • 4. Re: DBMS scheduler 3
    moreajays Pro
    moreajays Nov 26, 2012 5:58 AM (in response to 846231)
    Currently Being Moderated
    Below will give u id ,

    select job from dba_jobs where what='<job_name>';
  • 5. Re: DBMS scheduler 3
    Max Seleznev Explorer
    Max Seleznev Nov 26, 2012 9:00 AM (in response to 846231)
    Currently Being Moderated
    I might be mistaken, but it appears to me the OP is talking about Oracle scheduler (DBMS_SCHEDULER), not job manager (DBMS_JOB).

    In this case the following views could provide details of the execution:

    dba/all_scheduler_job_run_details
    dba/all_scheduler_job_log

    Sorry if I misunderstood the question.
  • 6. Re: DBMS scheduler 3
    846231 Newbie
    846231 Nov 26, 2012 6:11 PM (in response to Max Seleznev)
    Currently Being Moderated
    I thank you all,

    Well I guess dbms_job is under dbms_scheduler so I need to check all aspects of it :)
    There might be error affecting each part.
  • 7. Re: DBMS scheduler 3
    846231 Newbie
    846231 Nov 26, 2012 7:39 PM (in response to 846231)
    Currently Being Moderated
    Hi all,


    Its hard to monitor the dba logs tables as there are lots of rows and I dont know the jobs logging it :(
    How do I delete all the log tables? Are there other log tables like tha auditing tables that I need to periodically cleanup?
    It might cause space problem later.

    Thanks
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from dba_scheduler_job_run_details;

  COUNT(*)
----------
     15298

SQL> select count(*) from dba_scheduler_job_log;

  COUNT(*)
----------
     27132

SQL>
    Is it safe to truncate all the sched log tables?

    Edited by: KinsaKaUy? on 26-Nov-2012 19:38
  • 8. Re: DBMS scheduler 3
    Max Seleznev Explorer
    Max Seleznev Nov 26, 2012 8:11 PM (in response to 846231)
    Currently Being Moderated
    Please don't be discouraged by the amount of the information. By default the history is kept for 30 days which is not much. If you wish to keep less data you can always use
    exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history', N);
    where N is between 0 and 1000000 days.

    You can also search by job_name which you can find in DBA/ALL_SCHEDULER_JOBS to restrict the amount of info shown. Also there's a timestamp that make it easier to identify the last run.

    Edited by: Max Seleznev on Nov 26, 2012 11:10 PM
  • 9. Re: DBMS scheduler 3
    moreajays Pro
    moreajays Nov 27, 2012 1:24 AM (in response to Max Seleznev)
    Currently Being Moderated
    Hi,

    In Addition ..

    1. Use below to identify how many days logs preserved by scheduler views

    select max(LOG_DATE),min(LOG_DATE) from dba_scheduler_job_log;


    2. Try logging your jobs only when failure occurs

    to get job class ..

    select job_name,job_class from dba_scheduler_jobs where job_name='<your job name>';

    BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE (
    '<job_class>', 'logging_level', DBMS_SCHEDULER.LOGGING_FAILED_RUNS);
    END;

    Note : You must be granted the MANAGE SCHEDULER privilege to set the logging level of a job class.

    3. Purging Logs Manually

    exec DBMS_SCHEDULER.PURGE_LOG();


    Thanks,
    Ajay More

Actions

Legend

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