[ORACLE] Simple trick about DBMS_JOBS.

Hello everyone!!!

Hope you're doing well!

Today, I'm going to show you a simple trick about dbms_job. 

Ok, Ok!!! I know we should use the dbms_scheduler, but sometimes the customer uses an "old school" schedule, and they don't want to change.

In this case, if you run a creation with the SYS user, for example, the job will be created under the SYS User.

But you can change this using SYS.DBMS_IJOB.CHANGE_ENV.

For example:

exec dbms_job.submit (

   job       => jobno,
   what      => 'begin USER_DEST.proc; end;',
   next_date => SYSDATE+1,
   interval  => 'trunc(sysdate + 1) + 10/1440');   
  

SELECT 

JOB, 
LOG_USER,
PRIV_USER,
SCHEMA_USER,
LAST_DATE,
LAST_SEC,
WHAT 
FROM dba_jobs
WHERE JOB = 18068;

    JOB LOG_USER PRIV_USER SCHEMA_USER LAST_DATE LAST_SEC  WHAT
------- -------- --------- ----------- --------- --------- -------  

  18068 SYS     SYS    SYS        17-JUN-24 09:00:00 USER_DEST.proc;

If I need to change the owner:

EXEC SYS.DBMS_IJOB.CHANGE_ENV(
JOB    => 18068, 
LUSER  => 'USER_DEST', 
PUSER  => 'USER_DEST', 
CUSER  => 'USER_DEST', 
NLSENV => NULL);

Where:
JOB    => Job number 
LUSER  => Log user
PUSER  => Priv user
CUSER  => Schema User
NLSENV => NLS Language

Example:
-- Change only PRIV_USER
EXEC SYS.DBMS_IJOB.CHANGE_ENV(18068,NULL,'USER_DEST',NULL,NULL);
COMMIT;

-- Change all
EXEC SYS.DBMS_IJOB.CHANGE_ENV(18068,'USER_DEST','USER_DEST','USER_DEST',NULL);
COMMIT;

SELECT 

JOB, 
LOG_USER,
PRIV_USER,
SCHEMA_USER,
LAST_DATE,
LAST_SEC,
WHAT 
FROM dba_jobs
WHERE JOB = 18068;

    JOB LOG_USER   PRIV_USER  SCHEMA_USER LAST_DATE LAST_SEC  WHAT
------- ---------  ---------  ----------- --------- --------- -------

  18068 USER_DEST  USER_DEST  USER_DEST   17-JUN-24 09:00:00 USER_DEST.proc;

As simple as that!

I hope this helps you!

Regards

Mario