TIP for Oracle - Create/Remove jobs for other users

I'm sure every DBA is able to delete or send a task to other users if the administrator knows the password for another user, life will be easy and it's as simple as connecting with the user and send a job dbms_job.submit or remove it with dbms_job.remove.

What if DBA has not the password, If DBA tries to remove other user jobs, the following error will be shown :

ORA-23421: job number nnnn is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 536
ORA-06512: at "SYS.DBMS_JOB", line 261
ORA-06512: at line 1

Also DBA can not submit job with dbms_job for other users with dbms_job if DBA does not know the password.

Fortunately, Oracle has undocumented dbms_ijob package which let you as DBA to submit jobs as other users and also remove jobs of other users.

Here is a sample on how to submit a job for user TEST.


declare
job_num number;
nlsvar varchar2(4000);
envvar raw(32);
begin
select nls_env,misc_env into nlsvar,envvar from dba_jobs where rownum<2 and nls_env is not null and misc_env is not null ;
select max(job)+1 into job_num from dba_jobs;
sys.dbms_ijob.submit(job=>job_num,luser=>'TEST',puser=>'TEST',cuser=>'TEST',what=>'insert into tst values(1);' ,next_date=>sysdate+1/(24*60), interval=>'trunc(sysdate,''MI'')+1/48', broken=>FALSE,nlsenv=>nlsvar,env=>envvar);
dbms_output.put_line(job_num);
end;
/
commit;
nlsenv and env should not be passed NULL,In above sample to make it simple, I copied it from the existing jobs. puser (privileged user), luser (login user), cuser(schema user) are the same as what we have in dba_jobs. Job number needs to be passed to dbms_ijob, to make sure its uniqueness, a number higher than max job number is passed.

In order to remove any job , the following simple code can be run.


dbms_ijob.remove(job_number??);