Home » SQL & PL/SQL » SQL & PL/SQL » Scheduled job is not running automatically (merged)
Scheduled job is not running automatically (merged) [message #403509] Sat, 16 May 2009 01:43 Go to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hi all,

In previous post I was having problem with the ARGUMENTs of the PROGRAM. And I solved it by trial and error.

Now JOB and PROGRAM is created using DBMS_SCHEDULER package.
But problem here is that JOB is not running on the scheduled time automatically. If I run the JOB by RUN_JOB method then only it gets executed.

following is the output of all_scheduler_jobs columns having NOT NULL.
SQL>select owner, job_name, job_style, job_creator, client_id,
  2          program_owner, program_name, schedule_type, start_date,
  3          job_class, enabled, auto_drop, restartable, state,
  4          job_priority, run_count, failure_count, retry_count,
  5          next_run_date, logging_level, stop_on_window_close,
  6          instance_stickiness, system, job_weight, deferred_drop
  7  from all_scheduler_jobs
  8  where job_name = 'JOB_DIR_VERIFY37';

OWNER                         |JOB_NAME                      |JOB_STYLE
==============================|==============================|===========
JOB_CREATOR                   |CLIENT_ID
==============================|================================================================
PROGRAM_OWNER
====================================================================================================
PROGRAM_NAME
====================================================================================================
SCHEDULE_TYP|START_DATE
============|===========================================================================
JOB_CLASS                     |ENABL|AUTO_|RESTA|STATE          |JOB_PRIORITY| RUN_COUNT
==============================|=====|=====|=====|===============|============|==========
FAILURE_COUNT|RETRY_COUNT
=============|===========
NEXT_RUN_DATE                                                              |LOGGING_LEV|STOP_|INSTA
===========================================================================|===========|=====|=====
SYSTE|JOB_WEIGHT|DEFER
=====|==========|=====
XLNTSEO                       |JOB_DIR_VERIFY37              |REGULAR
APEX_PUBLIC_USER              |NARESH:619712768328050
XLNTSEO
PRG_DIR_VERIFY37
ONCE        |16-MAY-09 11.57.47.000000 AM +05:30
DEFAULT_JOB_CLASS             |TRUE |TRUE |FALSE|SCHEDULED      |           3|         0
            0|          0
16-MAY-09 11.57.47.000000 AM +05:30                                        |OFF        |FALSE|TRUE
FALSE|         1|FALSE


1 row selected.


And following is the all_scheduler_programs
SQL>select owner, program_name, program_type, program_action,
  2          number_of_arguments, enabled, detached, priority, weight 
  3  from all_scheduler_programs 
  4  where program_name = 'PRG_DIR_VERIFY37';

OWNER                         |PROGRAM_NAME                  |PROGRAM_TYPE
==============================|==============================|================
PROGRAM_ACTION
====================================================================================================
NUMBER_OF_ARGUMENTS|ENABL|DETAC|  PRIORITY|    WEIGHT
===================|=====|=====|==========|==========
XLNTSEO                       |PRG_DIR_VERIFY37              |STORED_PROCEDURE
SEO_VERIFICATION.VERIFY_DIR_SUBMISSION
                  5|TRUE |FALSE|         3|         1


1 row selected.


By seeing this fact, can anyone tell me that what is the problem with it in executing?

Thanks in advance.
Re: Scheduled job is not running automatically (merged) [message #403539 is a reply to message #403509] Sat, 16 May 2009 13:14 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I have not used jobs in a while so my knowledge on this topic may be a bit too old for you. Still... I remember several gotchas in the past:

1) if after submitting a job via dbms_job, you do not commit, the job is not created.

2) if the job is broken it will not run.

3) there used to be job_que_processes parameter. If no or insufficient job_queues exist, jobs will not run. This parameter was replaced by something else I believe, but some similar concept still applies I would think be it o/s threads, or oracle threads or whatever.

4) sometimes jobs run but do nothing becuase of the data. They still ran as far a oracle is concerned and this should be observable via the dba_jobs view.

5) jobs can be set up to run once or run periodically. A run once job would be deleted under the old rules so this may not apply to you. However I notice your "restart" column says false. Not sure what this means if anything.

Your all_scheduler_jobs is unfamiliar to me. I am an old school DBA_JOBS kind of guy.

Good luck, Kevin
Re: Scheduled job is not running automatically (merged) [message #404007 is a reply to message #403539] Wed, 20 May 2009 01:01 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Thank you Kevin sir for your concern.

Although I put COMMIT statement problem remains as it is.
Please have a look at following code if you can find any problem.

Following is the snippet of the code to create JOB.
...
   v_prog_name := 'Prg_DIR_VERIFY' || v_seq_value;
 81      v_job_name := 'Job_DIR_VERIFY' || v_seq_value;
 82      DBMS_SCHEDULER.CREATE_PROGRAM( program_name => v_prog_name,
 83                                          program_type => 'STORED_PROCEDURE',
 84                                          program_action=>'SEO_VERIFICATION.VERIFY_DIR_SUBMISSION',
 85                                          number_of_arguments => 5,
 86                                          enabled => FALSE,
 87                                          comments => NULL);
 88      DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(program_name => v_prog_name,
 89                                          argument_position => 1,
 90                                          argument_name => 'P_CLIENT_I',
 91                                          argument_type => 'NUMBER',
 92                                          default_value => P2005_CLIENT,
 93                                          out_argument => FALSE);
 94      DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(program_name => v_prog_name,
 95                                          argument_position => 2,
 96                                          argument_name => 'P_WEBSITE_I',
 97                                          argument_type => 'NUMBER',
 98                                          default_value => P2005_WEBSITE,
 99                                          out_argument => FALSE);
100      DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(program_name => v_prog_name,
101                                          argument_position => 3,
102                                          argument_name => 'P_STATUS_I',
103                                          argument_type => 'NUMBER',
104                                          default_value => P2005_STATUS,
105                                          out_argument => FALSE);
106      DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(program_name => v_prog_name,
107                                          argument_position => 4,
108                                          argument_name => 'P_SCHEDULE_I',
109                                          argument_type => 'NUMBER',
110                                          default_value => v_schedule_pk,
111                                          out_argument => FALSE);
112      DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(program_name => v_prog_name,
113                                          argument_position => 5,
114                                          argument_name => 'P_PROGRAM_NAME_I',
115                                          argument_type => 'VARCHAR2',
116                                          default_value => v_prog_name,
117                                          out_argument => FALSE);
118      DBMS_SCHEDULER.CREATE_JOB(      job_name => v_job_name,
119                                          program_name => v_prog_name,
120                                          start_date => SYSTIMESTAMP+0.0035,
121                                          repeat_interval => NULL,
122                                          end_date => NULL,
123                                          job_class => 'DEFAULT_JOB_CLASS',
124                                          enabled => FALSE,
125                                          auto_drop => TRUE,
126                                          comments => NULL);
127     COMMIT;
128      DBMS_SCHEDULER.ENABLE(v_prog_name);
129      DBMS_SCHEDULER.ENABLE(v_job_name);
130      commit;
...


and following are the details of JOB and PROGRAM attributes.
SQL>select owner, job_name, job_style, job_creator, client_id,
  2           program_owner, program_name, schedule_type, start_date,
  3           job_class, enabled, auto_drop, restartable, state,
  4           job_priority, run_count, failure_count, retry_count,
  5           next_run_date, logging_level, stop_on_window_close,
  6           instance_stickiness, system, job_weight, deferred_drop
  7    from all_scheduler_jobs
  8    where job_name = 'JOB_DIR_VERIFY88';

OWNER                         |JOB_NAME                      |JOB_STYLE  |JOB_CREATOR                   |CLIENT_ID                                                                                      
==============================|==============================|===========|==============================|================================================================                               
PROGRAM_OWNER                                                                                                                                                                                           
========================================================================================================================================================================================================
PROGRAM_NAME                                                                                                                                                                                            
========================================================================================================================================================================================================
SCHEDULE_TYP|START_DATE                                                                 |JOB_CLASS                     |ENABL|AUTO_|RESTA|STATE          |JOB_PRIORITY| RUN_COUNT|FAILURE_COUNT         
============|===========================================================================|==============================|=====|=====|=====|===============|============|==========|=============         
RETRY_COUNT|NEXT_RUN_DATE                                                              |LOGGING_LEV|STOP_|INSTA|SYSTE|JOB_WEIGHT|DEFER                                                                  
===========|===========================================================================|===========|=====|=====|=====|==========|=====                                                                  
XLNTSEO                       |JOB_DIR_VERIFY88              |REGULAR    |XLNTSEO                       |                                                                                               
XLNTSEO                                                                                                                                                                                                 
PRG_DIR_VERIFY88                                                                                                                                                                                        
ONCE        |20-MAY-09 11.16.57.000000 AM +05:30                                        |DEFAULT_JOB_CLASS             |TRUE |TRUE |FALSE|SCHEDULED      |           3|         0|            0         
          0|20-MAY-09 11.16.57.000000 AM +05:30                                        |OFF        |FALSE|TRUE |FALSE|         1|FALSE                                                                  
                                                                                                                                                                                                        

1 row selected.

SQL>select owner, program_name, program_type, program_action,
  2         number_of_arguments, enabled, detached, priority, weight
  3  from all_scheduler_programs
  4  where program_name = 'PRG_DIR_VERIFY88';

OWNER                         |PROGRAM_NAME                  |PROGRAM_TYPE                                                                                                                              
==============================|==============================|================                                                                                                                          
PROGRAM_ACTION                                                                                                                                                                                          
========================================================================================================================================================================================================
NUMBER_OF_ARGUMENTS|ENABL|DETAC|  PRIORITY|    WEIGHT                                                                                                                                                   
===================|=====|=====|==========|==========                                                                                                                                                   
XLNTSEO                       |PRG_DIR_VERIFY88              |STORED_PROCEDURE                                                                                                                          
SEO_VERIFICATION.VERIFY_DIR_SUBMISSION                                                                                                                                                                  
                  5|TRUE |FALSE|         3|         1                                                                                                                                                   
                                                                                                                                                                                                        

1 row selected.


as said earlier, JOB is not being executed on its scheduled time and that is the problem.
Please help me in this regard.

regards,
Delna
Re: Scheduled job is not running automatically (merged) [message #406450 is a reply to message #404007] Thu, 04 June 2009 01:47 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
Kevin,

This is dbms_scheduler, not dbms_job. A lot has changed.


Delna,

Is the problem that the job is not running at all or that it is not running when you expect it to? When are you expecting it to run? You have it set to run only once a few seconds after it is enabled or as soon after that as it can with no retries in case of problems and automatically dropped and not repeated.


Previous Topic: disk space get reduced due to procedure
Next Topic: How to connect table from different server using plsql
Goto Forum:
  


Current Time: Sun Dec 04 06:29:55 CST 2016

Total time taken to generate the page: 0.07223 seconds