Skip to Main Content
  • Questions
  • DBMS_SCHEDULER Programs are not being dropped automatically once the job completed

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rajendra Prasad.

Asked: March 23, 2017 - 12:35 pm UTC

Last updated: March 25, 2017 - 3:29 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi,

Whether the Programs created using DBMS Scheduler would be dropped automatically when the Job completed successfully ?

Thanks in advance !!


and Connor said...

No they are not. Programs are permanent objects in the database, eg


SQL> create or replace
  2  procedure my_proc is
  3  begin
  4    null;
  5  end;
  6  /

Procedure created.

SQL>
SQL> begin
  2    dbms_scheduler.create_program (
  3      program_name   => 'MY_PROGRAM',
  4      program_type   => 'PLSQL_BLOCK',
  5      program_action => 'begin my_proc; end;',
  6      enabled        => true);
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> begin
  2    DBMS_SCHEDULER.create_job (
  3      job_name      => 'MY_JOB',
  4      program_name  => 'MY_PROGRAM',
  5      start_date      => SYSTIMESTAMP,
  6      enabled       => TRUE);
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select job_name
  2  from dba_scheduler_jobs
  3  where owner = user;

no rows selected  -- ie, it has been run

SQL>
SQL> select program_name
  2  from dba_scheduler_programs
  3  where owner = user;

PROGRAM_NAME
--------------------------------------------------------------------------------------------------------------------------------
MY_PROGRAM

1 row selected.

SQL>
SQL>


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for your response

Rajendra Prasad Sindem, March 24, 2017 - 6:36 am UTC

Thanks for your quick response. Even i had the same thoughts but just wanted to be sure that i am not missing anything.
Connor McDonald
March 25, 2017 - 3:29 am UTC

glad we could help

Maybe this ....

J. Laurindo Chiappa, March 24, 2017 - 2:00 pm UTC

Hello, Rajendra : maybe you were thinking about the AUTO_DROP attribute in a DBMS_SCHEDULER job - see the Oracle docs ( https://docs.oracle.com/cd/B28359_01/server.111/b28310/scheduse002.htm#ADMIN12382 is for 11g) : a given job created via DBMS_SCHEDULER will be automatically dropped Only If you specify this attribute when creating the job - by default the auto-drop not will happen, just like Connor said...

Best regards,

J. Laurindo Chiappa
Connor McDonald
March 25, 2017 - 3:19 am UTC

Nice additional input.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library