1 2 Previous Next 20 Replies Latest reply on Apr 22, 2009 9:33 PM by rbglossip

    How to view Oracle job logs

    mohitanchlia
      Oracle 10g:

      I have following job:

      procedure create_or_replace_job as
      begin
      begin
      dbms_scheduler.drop_job(job_name => merge_job_name);
      exception
      when others then
      /* ignore */
      commit;
      end;
      dbms_scheduler.create_job(job_name => merge_job_name,
      job_type => 'STORED_PROCEDURE',
      job_action => merge_proc_name,
      start_date => to_timestamp('01-Jan-1970 ' || merge_job_time,
      'DD-Mon-RRRR HH24:MI:SS'),
      repeat_interval => 'FREQ = DAILY; INTERVAL = 1');
      commit;
      end;
      ---

      But I want to see when job ran and if there were any errors.
        • 1. Re: How to view Oracle job logs
          Aman....
          user628400 wrote:
          Oracle 10g:

          But I want to see when job ran and if there were any errors.
          You may want to check scheduler* views from doc for this. For example,
          [dba_scheduler_job_log|http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2047.htm#i1587038]
          [dba_scheduler_job_run_details|http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2048.htm#i1587156]
          HTH
          Aman....
          • 2. Re: How to view Oracle job logs
            591186
            all_scheduler_job_log will give you some information.
            • 3. Re: How to view Oracle job logs
              LKBrwn_DBA

              Would be easy if you use Enterprise Manager.
              Otherwise you need to look at:

              ALL_SCHEDULER_JOB_LOG view :p
              • 4. Re: How to view Oracle job logs
                247514
                Did you check DBA view dba_scheduler_job_run_details ?

                lol , I answered a quick phone call before I hit submit button, can't believe how many replies were posted during the interim.

                Edited by: yingkuan on Dec 17, 2008 9:23 AM
                • 5. Re: How to view Oracle job logs
                  Aman....
                  yingkuan wrote:
                  lol , I answered a quick phone call before I hit submit button, can't believe how many replies were posted during the interim.
                  That's why I was just sitting quietly and watching them flying all over :-).
                  Aman....
                  • 6. Re: How to view Oracle job logs
                    mohitanchlia
                    What could be the reason if I don't see anything in the logs? But I do know that the job got executed.
                    • 7. Re: How to view Oracle job logs
                      Aman....
                      user628400 wrote:
                      What could be the reason if I don't see anything in the logs? But I do know that the job got executed.
                      It may be that the job's log level is not properly set.
                      http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/scheduse002.htm#CHDGIDFD
                      HTH
                      Aman....
                      • 8. Re: How to view Oracle job logs
                        mohitanchlia
                        I am looking at how long it took for "GATHER_STATS_JOB" to run also when it started and when it ended. But none of the views that I queried give me that detail. I tried:

                        select * from dba_scheduler_job_run_details where job_name = 'GATHER_STATS_JOB' order by log_id desc
                        
                        LOG_ID     LOG_DATE     OWNER     JOB_NAME     JOB_SUBNAME     STATUS     ERROR#     REQ_START_DATE     ACTUAL_START_DATE     RUN_DURATION     INSTANCE_ID     SESSION_ID     SLAVE_PID     CPU_USED     ADDITIONAL_INFO     
                        1741          SYS     GATHER_STATS_JOB     <NULL>     SUCCEEDED     0     <NULL>               1     2316,20537     25785          <NULL>     
                        select * from dba_scheduler_jobs
                        
                        OWNER     JOB_NAME     JOB_SUBNAME     JOB_CREATOR     CLIENT_ID     GLOBAL_UID     PROGRAM_OWNER     PROGRAM_NAME     JOB_TYPE     JOB_ACTION     NUMBER_OF_ARGUMENTS     SCHEDULE_OWNER     SCHEDULE_NAME     SCHEDULE_TYPE     START_DATE     REPEAT_INTERVAL     EVENT_QUEUE_OWNER     EVENT_QUEUE_NAME     EVENT_QUEUE_AGENT     EVENT_CONDITION     EVENT_RULE     END_DATE     JOB_CLASS     ENABLED     AUTO_DROP     RESTARTABLE     STATE     JOB_PRIORITY     RUN_COUNT     MAX_RUNS     FAILURE_COUNT     MAX_FAILURES     RETRY_COUNT     LAST_START_DATE     LAST_RUN_DURATION     NEXT_RUN_DATE     SCHEDULE_LIMIT     MAX_RUN_DURATION     LOGGING_LEVEL     STOP_ON_WINDOW_CLOSE     INSTANCE_STICKINESS     RAISE_EVENTS     SYSTEM     JOB_WEIGHT     NLS_ENV     SOURCE     DESTINATION     COMMENTS     FLAGS     
                        SYS     GATHER_STATS_JOB     <NULL>     SYS     <NULL>     <NULL>     SYS     GATHER_STATS_PROG     <NULL>     <NULL>     <NULL>     SYS     MAINTENANCE_WINDOW_GROUP     WINDOW_GROUP     <NULL>     <NULL>     <NULL>     <NULL>     <NULL>     <NULL>     <NULL>     <NULL>     AUTO_TASKS_JOB_CLASS     TRUE     FALSE     TRUE     SCHEDULED     3     125     <NULL>     0     <NULL>     0               <NULL>     <NULL>     <NULL>     RUNS     TRUE     TRUE     <NULL>     TRUE     1     NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT='HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SSXFF AM TZR' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NLS_LENGTH_SEMANTICS='BYTE' NLS_NCHAR_CONV_EXCP='FALSE'     <NULL>     <NULL>     Oracle defined automatic optimizer statistics collection job     21074010     
                        select * from dba_schedular_schedules
                        
                        OWNER     SCHEDULE_NAME     SCHEDULE_TYPE     START_DATE     REPEAT_INTERVAL     EVENT_QUEUE_OWNER     EVENT_QUEUE_NAME     EVENT_QUEUE_AGENT     EVENT_CONDITION     END_DATE     COMMENTS     
                        SYS     DAILY_PURGE_SCHEDULE     CALENDAR     <NULL>     freq=daily;byhour=3;byminute=0;bysecond=0     <NULL>     <NULL>     <NULL>     <NULL>     <NULL>     <NULL>     
                        • 9. Re: How to view Oracle job logs
                          591186
                          Post the output of the below:
                          SELECT job_name, state FROM dba_scheduler_jobs WHERE job_name='GATHER_STATS_JOB';
                          
                          select job_name, job_type, program_name, schedule_name, job_class from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB';
                          
                          The actual_start_date and the run_duration will give you how long did it run.
                          
                          SQL> desc dba_scheduler_job_run_details
                           Name                                      Null?    Type
                           ----------------------------------------- -------- ----------------------------
                           LOG_ID                                             NUMBER
                           LOG_DATE                                           TIMESTAMP(6) WITH TIME ZONE
                           OWNER                                              VARCHAR2(30)
                           JOB_NAME                                           VARCHAR2(65)
                           JOB_SUBNAME                                        VARCHAR2(65)
                           STATUS                                             VARCHAR2(30)
                           ERROR#                                             NUMBER
                           REQ_START_DATE                                     TIMESTAMP(6) WITH TIME ZONE
                           ACTUAL_START_DATE                                  TIMESTAMP(6) WITH TIME ZONE
                           RUN_DURATION                                       INTERVAL DAY(3) TO SECOND(0)
                           INSTANCE_ID                                        NUMBER
                           SESSION_ID                                         VARCHAR2(30)
                           SLAVE_PID                                          VARCHAR2(30)
                           CPU_USED                                           INTERVAL DAY(3) TO SECOND(2)
                           ADDITIONAL_INFO                                    VARCHAR2(4000)
                          • 10. Re: How to view Oracle job logs
                            mohitanchlia
                            As you can see from my post above I tried that but it columns are empty. This is the jbos that Oracle runs every night.
                            • 11. Re: How to view Oracle job logs
                              rbglossip
                              It should be there.
                              SQL> select actual_start_date
                                2       , actual_start_date + run_duration end_date
                                3    from dba_scheduler_job_run_details
                                4   where log_id = (select max(log_id)
                                5                     from dba_scheduler_job_run_details
                                6                    where job_name = 'GATHER_STATS_JOB');
                              
                              ACTUAL_START_DATE
                              ------------------------------------------------------------
                              END_DATE
                              ------------------------------------------------------------
                              21-APR-09 10.00.02.244907 PM -04:00
                              21-APR-09 10.00.36.244907000 PM -04:00
                              Your output is a bit hard to read. Can you try the query above in SQL*Plus?
                              • 12. Re: How to view Oracle job logs
                                591186
                                oh. that was fast. I was just about to post the same... :D

                                may be, my browser was thinking :)

                                Edited by: Anantha on Apr 22, 2009 4:25 PM
                                • 13. Re: How to view Oracle job logs
                                  rbglossip
                                  Not too fast. Two replies posted while I was typing it ;)
                                  • 14. Re: How to view Oracle job logs
                                    591186
                                    Post the output of:
                                    SELECT job_name, state FROM dba_scheduler_jobs WHERE job_name='GATHER_STATS_JOB';
                                    
                                    select job_name, job_type, program_name, schedule_name, job_class from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB';
                                    1 2 Previous Next