3

I would like rename scheduler job in Oracle, is it possible?

dba_scheduler_jobs(owner = "db", name = "my_job")
=> dba_scheduler_jobs(owner = "db", name = "my_own_job");

Thanks for answers.

Jacob
  • 14,463
  • 65
  • 207
  • 320
Hadraigh
  • 31
  • 1
  • 2

2 Answers2

9

A job is a database object, so the RENAME command works natively

SQL> begin
  2    dbms_scheduler.create_job (
  3      job_name        => 'MY_BAD_NAME',
  4      job_type        => 'PLSQL_BLOCK',
  5      job_action      => 'begin null; end;',
  6      start_date      => systimestamp,
  7      repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
  8      enabled         => true);
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> rename MY_BAD_NAME to BETTER_NAME;

Table renamed.


SQL> select job_name from user_scheduler_jobs;

JOB_NAME
--------------------------------------------------------------------------------
BETTER_NAME
Connor McDonald
  • 10,418
  • 1
  • 11
  • 16
  • Hi, thanks for answer, but it doesn't work. I'm using pl/sql developer and of course I use the schema too. and when I try: alter object my_schema.my_job rename to my_own_job; it finish with error. – Hadraigh Jan 22 '20 at 14:17
  • why did you run 'alter ... rename?' Connor showed just do 'rename old_job to new_job' – thatjeffsmith Jan 22 '20 at 23:22
  • This is a neat trick, but is this syntax supported? The [SQL Language Reference](https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/RENAME.html#GUID-573347CE-3EB8-42E5-B4D5-EF71CA06FAFC) says "Use the RENAME statement to rename a table, view, sequence, or private synonym." – Jon Heller Jan 23 '20 at 01:08
  • 2
    I'll file a doc bug for that. Thanks for letting us know – Connor McDonald Jan 23 '20 at 02:42
0

Instead of rename, you can create/replicate job and drop the old one.

Below, my_new_job1 has created.

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name          =>  'my_new_job1',
   program_name      =>  'my_saved_program', 
   repeat_interval   =>  'FREQ=DAILY;BYHOUR=12',
   comments          =>  'Daily at noon');
END;
/

Below, my_new_job2 has created with same details as my_new_job1

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name          =>  'my_new_job2',
   program_name      =>  'my_saved_program', 
   repeat_interval   =>  'FREQ=DAILY;BYHOUR=12',
   comments          =>  'Daily at noon');
END;
/

Below an old job my_new_job1 has been deleted.

BEGIN
DBMS_SCHEDULER.DROP_JOB ('my_new_job1');
END;
/
Sandesh Herwade
  • 21
  • 5