segunda-feira, 17 de junho de 2024

[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

Nenhum comentário:

Postar um comentário

Isso te ajudou? Comente...

Postagem em destaque

[ORACLE] Embedding ONNX model for Vector Search tests with Oracle Database 23ai

Hello, everyone. How's it going? Today, I'm going to show you how to load an ONNX model into the Oracle 23ai database for your vecto...