Tuesday, August 9, 2011

How to check and disable automated jobs in 11g



This query will give you the names and the status of the jobs scheduled by DBMS_SCHEDULER:

SELECT job_name,enabled  FROM DBA_SCHEDULER_JOBs;

Then use "exec dbms_scheduler.disable" to disable each one as below:

I created a script "disablejobs.sql" as :

exec dbms_scheduler.disable('SYS.XMLDB_NFS_CLEANUP_JOB');
exec dbms_scheduler.disable('SYS.SM$CLEAN_AUTO_SPLIT_MERGE');
exec dbms_scheduler.disable('SYS.RSE$CLEAN_RECOVERABLE_SCRIPT');
exec dbms_scheduler.disable('SYS.FGR$AUTOPURGE_JOB');
exec dbms_scheduler.disable('SYS.BSLN_MAINTAIN_STATS_JOB');
exec dbms_scheduler.disable('SYS.DRA_REEVALUATE_OPEN_FAILURES');
exec dbms_scheduler.disable('SYS.HM_CREATE_OFFLINE_DICTIONARY');
exec dbms_scheduler.disable('SYS.ORA$AUTOTASK_CLEAN');
exec dbms_scheduler.disable('SYS.FILE_WATCHER');
exec dbms_scheduler.disable('SYS.PURGE_LOG');
exec dbms_scheduler.disable('SYS.MGMT_STATS_CONFIG_JOB');
exec dbms_scheduler.disable('SYS.MGMT_CONFIG_JOB');
exec dbms_scheduler.disable('SYS.RLM$SCHDNEGACTION');
exec dbms_scheduler.disable('SYS.RLM$EVTCLEANUP');



and then executed:

SQL> @disablejobs.sql

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

BEGIN dbms_scheduler.disable('SYS.MGMT_STATS_CONFIG_JOB'); END;

*
ERROR at line 1:
ORA-27476: "SYS.MGMT_STATS_CONFIG_JOB" does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 4355
ORA-06512: at "SYS.DBMS_SCHEDULER", line 2736
ORA-06512: at line 1


BEGIN dbms_scheduler.disable('SYS.MGMT_CONFIG_JOB'); END;

*
ERROR at line 1:
ORA-27476: "SYS.MGMT_CONFIG_JOB" does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 4355
ORA-06512: at "SYS.DBMS_SCHEDULER", line 2736
ORA-06512: at line 1


BEGIN dbms_scheduler.disable('SYS.RLM$SCHDNEGACTION'); END;

*
ERROR at line 1:
ORA-27476: "SYS.RLM$SCHDNEGACTION" does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 4355
ORA-06512: at "SYS.DBMS_SCHEDULER", line 2736
ORA-06512: at line 1


BEGIN dbms_scheduler.disable('SYS.RLM$EVTCLEANUP'); END;

*
ERROR at line 1:
ORA-27476: "SYS.RLM$EVTCLEANUP" does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 4355
ORA-06512: at "SYS.DBMS_SCHEDULER", line 2736
ORA-06512: at line 1



SQL> SELECT job_name,enabled  FROM DBA_SCHEDULER_JOBs;

JOB_NAME                                         ENABL
------------------------------ ----- ---------------------------------
XMLDB_NFS_CLEANUP_JOB                         FALSE
SM$CLEAN_AUTO_SPLIT_MERGE                FALSE
RSE$CLEAN_RECOVERABLE_SCRIPT           FALSE
FGR$AUTOPURGE_JOB                                    FALSE
BSLN_MAINTAIN_STATS_JOB                        FALSE
DRA_REEVALUATE_OPEN_FAILURES            FALSE
HM_CREATE_OFFLINE_DICTIONARY            FALSE
ORA$AUTOTASK_CLEAN                                  FALSE
FILE_WATCHER                                                   FALSE
PURGE_LOG                                                         FALSE
MGMT_STATS_CONFIG_JOB                            TRUE
MGMT_CONFIG_JOB                                          TRUE
RLM$SCHDNEGACTION                                    TRUE
RLM$EVTCLEANUP                                             TRUE

14 rows selected.

3 comments:

  1. Hello dear,
    I have error massage in my production environment, details for given below:
    ORA-12012: error on auto execute of job \"SYS\".\"DRA_REEVALUATE_OPEN_FAILURES\"
    ORA-48223: Interrupt Requested - Fetch Aborted - Return Code [51111] [HM_FINDING]
    ORA-48223: Interrupt Requested - Fetch Aborted - ORA-06512: at \"SYS.DBMS_IR\", line 522
    ORA-51108: unable to access diagnostic repository - retry command

    If I disable the DRA_REEVALUATE_OPEN_FAILURES job as you mentioned in your post, Is there any problem??

    Thanks for your help.

    ReplyDelete
  2. Replies
    1. OK, I did and message appearing in alert file is stopped, but what is the cause of these error,, many blogs says because of locking.. but what locks and from where I can find all these locks. I wanna lock for reason , not just disable the job. Thanks for your time and help.

      Delete