Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2006
    Location
    Latrobe, PA
    Posts
    16

    Unanswered: Startup trigger to execute dbms_job.run

    Oracle 9iR2. HPUX 11.11

    I have a job that runs every two seconds. When the database gets bounced the job no longer runs after it is started up.

    I created a startup trigger to run dbms_job.run, but in the alert log I am getting the following:

    Errors in file /oracle/X01/saptrace/usertrace/x01_ora_19253.trc:
    ORA-12012: error on auto execute of job 1538
    ORA-04092: cannot COMMIT in a trigger
    Mon Dec 18 10:17:09 2006
    Errors in file /oracle/X01/saptrace/usertrace/x01_ora_19253.trc:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-04092: cannot ROLLBACK in a trigger
    ORA-12012: error on auto execute of job 1538
    ORA-04092: cannot COMMIT in a trigger
    ORA-06512: at "SYS.DBMS_IJOB", line 406
    ORA-06512: at "SYS.DBMS_JOB", line 272
    ORA-06512: at line 10


    HERE IS THE TRIGGER:

    CREATE OR REPLACE TRIGGER "SYS"."START_SHARED_MEM_MAX_PROC" AFTER STARTUP ON DATABASE
    DECLARE

    v_job NUMBER;

    BEGIN

    SELECT job INTO v_job
    FROM dba_jobs
    WHERE WHAT = 'ZSHARED_MEMORY_MAX;';

    DBMS_JOB.RUN (v_job);

    END;
    /

  2. #2
    Join Date
    Dec 2006
    Location
    Latrobe, PA
    Posts
    16
    Nevermind...I see that the job_queue_processes was set to zero in the init.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •