This post is also available in: Bulgarian
I had to do this recently, and I will write down the scripts for future reference. The request was to recreate specific database jobs with a new user. But the scripts can be adapted for any bulk change of database jobs.
For the standard dbms_job I used the following:
create or replace function get_dbms_job_ddl(v_job in number) return varchar2 is v_tmp varchar2(4000); begin dbms_ijob.full_export(v_job, v_tmp); return v_tmp; end; / select 'dbms_ijob.remove(' || job || ');' from dba_jobs where log_user = 'OLDUSER'; select replace(get_dbms_job_ddl(job), '''OLDUSER''', '''NEWUSER''') from dba_jobs where log_user = 'OLDUSER';
If you have some job with longer
WHAT, you may have to use
CLOB instead of
Also be careful with the
REPLACE, as it may break something in the job. So proofread the produced script.
DBMS_SCHEDULER jobs I used (warning! requires additional changes! please read the comments after the script):
select 'exec DBMS_SCHEDULER.drop_job (job_name => ''' || owner || '.' || job_name || ''');' from dba_scheduler_jobs where job_creator = 'OLDUSER'; select dbms_metadata.get_ddl('PROCOBJ', job_name, owner) from dba_scheduler_jobs where job_creator = 'OLDUSER';
I must say that
DBMS_METADATA has some difficulties on this matter. First, the type of an object to be exported is ‘PROCOBJ’ (PROCedural OBJect), which sounds to me like some quick-and-dirty patch to the code. It also generated tons of quotes and double quotes, which I had to
replace in a text editor. And the real problem is, it did not put and “owner” in the job creation scripts, so I had to put it manually.
Anyway, id helped me a lot.