0

Hi，everyone，I am new to DB or Oracle. I got a problem here which hope you can help me:

I have 400+ specific scheduler jobs running,they are running hourly ,now I want all of them paused until I need them running again. How can I do that easily? Now I can just change it on webpage, could you help? Really appreciate it.

Finch
  • 1
  • 1

2 Answers2

2

List currently running jobs for a specific DB User:

SELECT JOB_NAME, ENABLED, OWNER FROM DBA_SCHEDULER_JOBS
WHERE OWNER='<DESIRED_SCHEMA_NAME>' AND ENABLED='TRUE';

Disabling running job by using: 

BEGIN
  FOR X IN (SELECT JOB_NAME, OWNER FROM DBA_SCHEDULER_JOBS WHERE OWNER='<DESIRED_SCHEMA_NAME>' AND ENABLED='TRUE')
  LOOP
     DBMS_SCHEDULER.DISABLE(CONCAT(CONCAT(X.OWNER,'.'), X.JOB_NAME));
  END LOOP;
END;
Duong
  • 465
  • 5
  • 13
  • thanks a lot！I’ve test it with single job and it’s working! But I have another question, how do I change all the jobs that i need? What I want to change is all the tasks name like “F_KH_%”(as F_KH_A1303 & F_KH_B1543 and so on)? Could you please help me more? – Finch Jun 12 '18 at 02:13
  • Adding condition to sql: AND JOB_NAME LIKE 'F_KH_%'; Copy result to excel file and =concat("EXEC DBMS_SCHEDULER.DISABLE('", JOB_NAME, "');"). Put result between begin ... end; and run. :). --- Someone good at PL/SQL please change my ideas into PL/SQL code --- – Duong Jun 12 '18 at 02:26
  • In a PL/SQL block as shown above, you shouldn't put `EXEC` before `DBMS_SCHEDULER.DISABLE(...`. If you're running this procedure from an SQL*Plus command line window, on the other hand, you **do** need the `EXEC`. Best of luck. – Bob Jarvis - Слава Україні Jun 12 '18 at 02:37
  • I've update PL/SQL and unintentionally delete previous updater's result! Really sorry ... – Duong Jun 12 '18 at 07:17
1

You may review your design. Having hundreds of jobs in one database seems to be rather strange. I would assume you can join most of them in a stored procedure and then the scheduler jobs execute such procedure.

Anyway, would be like this:

declare
   cursor SchedJobs is
   select  JOB_NAME, ENABLED, OWNER 
   FROM DBA_SCHEDULER_JOBS
   WHERE OWNER='<DESIRED_SCHEMA_NAME>' 
      AND ENABLED='TRUE'
      AND JOB_NAME LIKE 'F_KH_%';
BEGIN
  FOR aJob in SchedJobs  LOOP
     DBMS_SCHEDULER.DISABLE(aJob.OWNER||'.'||aJob.JOB_NAME);
  END LOOP;    
END;
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110