If you are scheduling jobs on a RAC it would be nice to be able to control which instance the job will run in. This is easy to do using job classes with these steps:
- Create a new job class and assign a service name to the class
- Grant execute in that job class to all the Oracle accounts that you want to use it with
- Assign the new job class to existing (or new) jobs.
Here are some SQL snippets to help with steps 2 and 3:
— make executable grants for job classes where needed
select distinct ‘grant execute on SYS.’||o.object_name||’ to ‘||j.owner || ‘;’
from dba_objects o, dba_scheduler_jobs j
where o.object_type = ‘JOB CLASS’ and o.oracle_maintained = ‘N’ and j.owner not like ‘SYS%’
minus
select distinct ‘grant execute on SYS.’||p.table_name||’ to ‘||p.grantee || ‘;’
from dba_tab_privs p where p.privilege = ‘EXECUTE’ and type = ‘JOB CLASS’;
@fix job classes@
— fix job classes
select owner,job_name,job_class,’exec SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(name => ”’||owner||’.’||job_name||”’, attribute => ”JOB_CLASS”, value => ”ETL_JOB_CLASS”);’
from dba_scheduler_jobs j where owner not in (‘SYS’,’APEX_040200′) and job_class = ‘DEFAULT_JOB_CLASS’
order by 1, 2;