怎么一次性禁用10g中的dbms_scheduler类job

bibosea

For Oracle version 11.2 onwards setting job_queue_processes=0 disables all jobs (dbms_scheduler and dbms_job).

10g中，job_queue_processes=0对dbms_scheduler类job无效。

begin
dbms_scheduler.set_scheduler_attribute('MAX_JOB_SLAVE_PROCESSES', value);
end;

value要大于0.

还有其他方法没？


谢谢

Maclean Liu(刘相兵

ODM Finding:

1. /*************************************************************
   
2. * Special Scheduler Administrative Procedures
   
3. *************************************************************
   
4. */
   
5. 
   
6. -- There are several scheduler attributes that control the behavior of the
   
7. -- scheduler. These have defaults but a DBA may wish to change the default
   
8. -- settings or view the current settings. These two functions are provided for
   
9. -- this purpose.
   
10. -- Even though the scheduler attributes have different types (e.g. strings,
    
11. -- numbers) all the values are passed as string literals. The set
    
12. -- procedure requires the MANAGE SCHEDULER privilege.
    
13. -- This takes effect immediately, but the resulting changes may not be seen
    
14. -- immediately.
    
15. -- Attributes which may be set are:
    
16. -- 'MAX_SLAVE_PROCESSES'(pls_integer), 'DEFAULT_LOG_PURGE_POLICY'(varchar2),
    
17. -- 'LOG_HISTORY' (pls_integer)
    
18. 
    
19. -- Set the value of a scheduler attribute. This takes effect immediately,
    
20. -- but the resulting changes may not be seen immediately.
    
21. PROCEDURE set_scheduler_attribute(
    
22.   attribute          IN VARCHAR2,
    
23.   value              IN VARCHAR2);

复制代码

Let's Test This procedure:


SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE');

PL/SQL procedure successfully completed.


SQL>  select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE ;

ATTRIBUTE_NAME                           VALUE
---------------------------------------- ----------------------------------------
MAX_JOB_SLAVE_PROCESSES
LOG_HISTORY                              30
DEFAULT_TIMEZONE                         EST5EDT
EMAIL_SERVER
EMAIL_SERVER_ENCRYPTION                  NONE
EMAIL_SERVER_CREDENTIAL
EMAIL_SENDER
LAST_OBSERVED_EVENT
EVENT_EXPIRY_TIME
FILE_WATCHER_COUNT                       0
CURRENT_OPEN_WINDOW                      WEDNESDAY_WINDOW
SCHEDULER_DISABLED                       TRUE

12 rows selected.


SQL> select * from dba_objects where object_name='SCHEDULER_DISABLED';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S  NAMESPACE
--------- --------- ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
SYS
SCHEDULER_DISABLED
                                    81917                UNDEFINED
14-MAR-12 14-MAR-12 2012-03-14:23:11:58 VALID   N N N         51

SQL>
SQL> DECLARE
  2   x VARCHAR2(100);
  3  BEGIN
  4    dbms_scheduler.get_scheduler_attribute('DEFAULT_TIMEZONE', x);
  5    dbms_output.put_line('DTZ: ' || x);
  6    dbms_scheduler.get_scheduler_attribute('EVENT_EXPIRY_TIME', x);
  dbms_output.put_line('EET: ' || x);
  7    8    dbms_scheduler.get_scheduler_attribute('LOG_HISTORY', x);
  9    dbms_output.put_line('LH: ' || x);
10    dbms_scheduler.get_scheduler_attribute('MAX_JOB_SLAVE_PROCESSES', x);
11    dbms_output.put_line('MJSP: ' || x);
12    dbms_scheduler.get_scheduler_attribute('SCHEDULER_DISABLED', x);
13    dbms_output.put_line('SCHEDULER_DISABLED: ' || x);
14  END;
15  /
DTZ: EST5EDT
EET:
LH: 30
MJSP:
SCHEDULER_DISABLED: TRUE

PL/SQL procedure successfully completed.


SQL> BEGIN
  2    dbms_scheduler.create_job(
  3    job_name => 'NULL_JOB',
  4    job_type=>'PLSQL_BLOCK',
  5    job_action=>' begin NULL; end;',
  6    start_date => dbms_scheduler.stime,
  7    repeat_interval => 'FREQ=MINUTELY;INTERVAL=1',
  8    end_date => dbms_scheduler.stime+1,
  9    enabled => TRUE,
10    auto_drop => FALSE,
11    comments => 'MacleanS JOB');
12  END;
13  /


SQL> select * from dba_scheduler_job_log where job_name='NULL_JOB';

no rows selected

SQL> /

no rows selected


SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','FALSE');

PL/SQL procedure successfully completed.


SQL> select * from dba_objects where object_name='SCHEDULER_DISABLED';

no rows selected


SQL> select count(*) from dba_scheduler_job_log where job_name='NULL_JOB';

  COUNT(*)
----------
         1


SQL> select count(*) from dba_scheduler_job_log where job_name='NULL_JOB';

  COUNT(*)
----------
         2

Maclean Liu(刘相兵

ODM Finding:

1. To Ensure that the Repository DBMS_Jobs are always Executed:
   
2. 
   
3. 1. Set the job_queue_processes initialization parameter to a value > 0. For a Grid Control repository, the recommended value is 10. This should be set to a higher value if there are other user-defined dbms_job's in the same database.
   
4. 
   
5. 2. Verify that the CJQ0 process / Scheduler is not disabled. Login to the database as a DBA user and execute:
   
6. SQL> select * from dba_objects where object_name='SCHEDULER_DISABLED';
   
7. 
   
8. If this returns:
   
9. 
   
10. OWNER                         OBJECT_NAME
    
11. -----------------------       ---------------------------------
    
12. SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
    
13. ------------------------------ ---------- -------------- -------------------
    
14. CREATED LAST_DDL_ TIMESTAMP STATUS              T G S
    
15. --------- --------- ------------------- ------- - - -
    
16. SYS                           SCHEDULER_DISABLED
    
17. 379669 UNDEFINED
    
18. 02-AUG-10 02-AUG-10 2010-08-02:13:46:20 VALID N N N
    
19. 
    
20. then the CJQ process will not be started automatically with the Database startup. To rectify this,
    
21. 
    
22. SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','FALSE');
    
23. 
    
24. Verify that the CJQ is now running:
    
25. 
    
26. SQL> select program, username from v$process;
    
27. 
    
28. PROGRAM USERNAME
    
29. -----------------------------------  ---------------
    
30. ...
    
31. oracle@em11gc.idc.oracle.com (CJQ0)  oracle
    
32. ...
    
33. 
    
34. -  In case of a RAC database, ensure that the value of the job_queue_processes initialization parameter is the same across all the instances.  Use the query:
    
35. 
    
36. SQL> SELECT a.instance_name AS sid, b.value AS jobqueue
    
37. FROM gv$instance a, gv$parameter b
    
38. WHERE a.inst_id = b.inst_id
    
39. AND b.name='job_queue_processes';

复制代码

bibosea

非常感谢LIU      ！