Apr 122013

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);
	dbms_ijob.full_export(v_job, v_tmp);
	return v_tmp;

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 varchar2(4000).
Also be careful with the REPLACE, as it may break something in the job. So proofread the produced script.

For 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.

 Posted by at 8:58

Sorry, the comment form is closed at this time.