Tuesday, September 21, 2010

How to prevent scheduled jobs to run at instance startup?

The solution is very simple:

1. startup in restricted mode
startup restrict;


2. disable all windows
select 'exec DBMS_SCHEDULER.DISABLE('''||WINDOW_NAME||''');' from ALL_SCHEDULER_WINDOWS where ENABLED='TRUE';


3. close all windows
select 'exec DBMS_SCHEDULER.CLOSE_WINDOW('''||WINDOW_NAME||''');' from ALL_SCHEDULER_WINDOWS where ACTIVE='TRUE';


4. stop running & disable jobs

CASE WHEN state !='RUNNING' THEN 'begin'  || CHR(10)  || 'DBMS_SCHEDULER.STOP_JOB ('''||OWNER||'.'||JOB_NAME||''', TRUE); '  || CHR(10)  || 'EXCEPTION WHEN OTHERS THEN NULL;'  || CHR(10)  || 'END; ' || CHR(10)  || '/' || CHR(10)  || 'exec DBMS_SCHEDULER.DISABLE ('''||OWNER||'.'||JOB_NAME||''', TRUE );'
ELSE
'exec DBMS_SCHEDULER.DISABLE ('''||OWNER||'.'||JOB_NAME||''', true);' 
end DDD
FROM DBA_SCHEDULER_JOBS
where OWNER not in ('SYS','EXFSYS','ORACLE_OCM');"| sqlplus -S '/ as sysdba'

5. shutdown immediate

6. startup

No comments:

Post a Comment