Scheduler Jobs Queries

Query for running Scheduler Jobs:

select s.username, rj.job_name, rj.running_instance, s.sid, s.serial#, p.spid, s.lockwait, s.logon_time
from dba_scheduler_running_jobs rj,
     gv$session s,
     gv$process p
where rj.running_instance = s.inst_id
and   rj.session_id = s.sid
and   s.inst_id = p.inst_id
and   s.paddr = p.addr
–and   p.spid = <spid>
order by s.username, rj.job_name;

Query for seeing broken/failing jobs:

select ‘DB1’ as DB, Owner, Job_name, Enabled, Run_Count, failure_count Fails, max_failures  Max,  NVL(TO_CHAR(LAST_START_DATE,‘YYYY-MM-DD
HH24:MI:SS’
),‘NULL

) Last_start_Date

from dba_scheduler_jobs

where (max_failures failure_count) <= 2

order by failure_count desc;

Dynamically Disable All Scheduler Jobs:

BEGIN
    DBMS_OUTPUT.ENABLE(9000000
);
    FOR cur
IN
        (SELECT ‘BEGIN SYS.DBMS_SCHEDULER.STOP_JOB(JOB_NAME => ‘ || chr(39) || OWNER || ‘.’ || JOB_NAME || chr(39) || ‘, FORCE => TRUE); END;’ stmt
,
        OWNER || ‘.’
|| JOB_NAME job_name
        FROM
DBA_SCHEDULER_JOBS
        WHERE OWNER <> ‘SYS’)
   
LOOP
       
–DBMS_OUTPUT.PUT_LINE(cur.stmt);
       
BEGIN
            EXECUTE IMMEDIATE(cur.stmt
);
        EXCEPTION

        WHEN others THEN
            DBMS_OUTPUT.PUT_LINE(‘JOB NOT RUNNING ‘ || cur.job_name
);
           
–DBMS_OUTPUT.PUT_LINE(SQLERRM || ‘ CAUSE:’ || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
       
END;
    END LOOP;

    FOR cur IN
        (SELECT ‘BEGIN SYS.DBMS_SCHEDULER.DISABLE(NAME => ‘ || ”” || OWNER || ‘.’ || JOB_NAME || ”” || ‘, FORCE => TRUE); END;’ stmt
,
        OWNER || ‘.’
|| JOB_NAME job_name
        FROM
DBA_SCHEDULER_JOBS
        WHERE OWNER <> ‘SYS’
)
    LOOP

        –DBMS_OUTPUT.PUT_LINE(cur.stmt);
       
BEGIN
            EXECUTE IMMEDIATE(cur.stmt
);
       
EXCEPTION
        WHEN others
THEN
            DBMS_OUTPUT.PUT_LINE(‘JOB NOT DISABLED ‘ || cur.job_name
);
           
–DBMS_OUTPUT.PUT_LINE(SQLERRM || ‘ CAUSE:’ || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
       
END;
    END LOOP
;
END;
/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: