Re: disable SNP job processing temporarily

[Igor Neyman]

Title: disable SNP job processing temporarily

1. 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 [EMAIL PROTECTED]       ----- 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).