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');
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;
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);
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 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;
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;
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...