Re: disable SNP job processing temporarily

disable SNP job processing temporarily1. Mark all your jobs as "broken": dbms_job.broken(job_no, TRUE). declare nJ int;

JobNum_Array DBMS_SQL.NUMBER_TABLE;

begin

SELECT job BULK COLLECT INTO JobNum_Array

FROM dba_jobs;

FOR nJ IN 1..JobNum_Array.COUNT LOOP

DBMS_JOB.BROKEN(JobNum_Array(nJ), TRUE);

END LOOP; commit;

DBMS_LOCK.SLEEP(60); EXCEPTION WHEN NO_DATA_FOUND THEN NULL; end;

/

2. Wait untill there is no records in dba_jobs_running. declare lCount int := 1;

BEGIN WHILE (lCount > 0) LOOP

SELECT COUNT(*) INTO lCount

FROM dba_jobs_running;

DBMS_LOCK.SLEEP(20); END LOOP; END;
/

3. Recompile your packages.

4. Mark all your jobs as "un-broken": dbms_job.broken(job_no, FALSE, next_date), specifying proper "next_date".

Igor Neyman, OCP DBA
ineyman_at_perceptron.com   

• Original Message ----- From: Markham, Richard To: Multiple recipients of list ORACLE-L Sent: Monday, November 04, 2002 9:43 AM Subject: disable SNP job processing temporarily

  I have job_queue_processes 5 and job_queue_interval 3   I need to release locks on packages for a recompile   but ALTER SYSTEM ENABLE RESTRICTED SESSION; is not   always an option for me. Does anyone know what I can   do to suspend job processing (with minimal impact).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Igor Neyman
  INET: ineyman_at_perceptron.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).