1

I have below oracle job.

dbms_scheduler.create_job
                   (job_name                 => m_job_name,
                    job_type                 => 'PLSQL_BLOCK',
                    job_action               =>    'begin Pkg_Shell.PR_WF_PROC('
                                                || p_seq_request
                                                || '); end;',
                    number_of_arguments      => 0,
                    start_date               => sysdate,               
                    repeat_interval          => null,
                    end_date                 => null,                                       
                    job_class                => 'DEFAULT_JOB_CLASS',
                    enabled                  => false,
                    auto_drop                => true,
                    comments                 => null
                   );

The above job is not dropping automatically. This job will run only once. When i went thru various sites it says 

For auto drop,This flag if TRUE, causes a job to be automatically dropped after it has completed or has been automatically disabled. A job is considered completed if:
1.Its end date (or the end date of the job schedule) has passed.
2.It has run max_runs number of times. max_runs must be set with SET_ATTRIBUTE.
3.It is not a repeating job and has run once.

My job will run only once. Why my job is not Auto dropped in certain scenarios. ? We couldnt find when it is not dropped. To over come this If i want to mention end_date like sysdate + 2 hours how to mention it ? If i want to set max_runs or max_fails how to use that in my job. ? Whether these two settings or anyone above will solve my problem ?

Karthik
  • 545
  • 6
  • 23
  • version? Did the job run at all? –  Aug 18 '14 at 12:05
  • Oracle 9i. Yes job is executing fine and applications are processing fine. But after execution its not dropping – Karthik Aug 19 '14 at 08:56
  • 9i .... AFAIK 10gR1 was the first with dbms_scheduler. –  Aug 19 '14 at 08:58
  • We have created new schema in oracle 11g and tested with the same. Still we are facing the same issue. Jobs are not dropping. In production we have about 8 million jobs and which causing performance issue. Production is 10g and we are testing now in uat – Karthik Aug 22 '14 at 06:14

1 Answers1

1

After so long i found the below links that helped me to fix my issue. I have used max_runs set to 1 .

dbms_scheduler.set_attribute(m_job_name,'max_runs',1);

https://community.oracle.com/thread/936850

https://community.oracle.com/message/2458833#2458833

Karthik
  • 545
  • 6
  • 23