FAQ
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: Markham, Richard
INET: RMarkham_at_hafeleamericas.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).

Search Discussions

  • Jeremiah Wilton at Nov 4, 2002 at 3:18 pm
    How about:

    alter system set job_queue_processes = 0;
    wait for nothing to be running in dba_jobs_running
    do your stuff
    alter system set job_queue_processes = 5;

    --
    Jeremiah Wilton
    http://www.speakeasy.net/~jwilton
    On Mon, 4 Nov 2002, Markham, Richard wrote:

    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: Jeremiah Wilton
    INET: jwilton_at_speakeasy.net

    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).
  • Igor Neyman at Nov 4, 2002 at 3:28 pm
    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).
  • Jack van Zanen at Nov 4, 2002 at 3:34 pm
    Would alter system set job_queue_processes = 0 be anoption???



    Jack

    -----Original Message-----
    Sent: maandag 4 november 2002 15:44
    To: Multiple recipients of list ORACLE-L

    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: Jack van Zanen
    INET: JACK_at_QUANTSYSTEMS.NL

    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).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 4, '02 at 2:43p
activeNov 4, '02 at 3:34p
posts4
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase