1 2 Previous Next 29 Replies Latest reply: Mar 16, 2007 9:05 AM by 557989 RSS

    ORA-02289: sequence does not exist

    torch
      Hi all. ORACLE Newbie alert!

      I am using TOAD for Oracle 8.6

      The Oracle database version is

      BANNER
      Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
      PL/SQL Release 9.2.0.6.0 - Production
      CORE     9.2.0.6.0     Production
      TNS for Solaris: Version 9.2.0.6.0 - Production
      NLSRTL Version 9.2.0.6.0 - Production

      I have been tasked with monitoring a table from another schema, using a different account.


      The first thing I did was create this table in my SOMERSET schema

      CREATE TABLE MONITOR_PERSONS
      (
      IDENTIFIER INTEGER,
      OLD_FAMILY_NAME VARCHAR2(40 BYTE),
      OLD_FIRST_NAMES VARCHAR2(40 BYTE),
      NEW_FAMILY_NAME VARCHAR2(40 BYTE),
      NEW_FIRST_NAMES VARCHAR2(40 BYTE),
      UPDATE_DATE DATE,
      UPDATE_STATUS CHAR(1 CHAR),
      PERSONS_IDENTIFIER INTEGER
      )


      then a SEQUENCE to be used by the above table

      CREATE SEQUENCE SOMERSET.MONITOR_PERSONS_SEQ
      START WITH 1
      MAXVALUE 999999999999999999999999999
      MINVALUE 1
      NOCYCLE
      NOCACHE
      NOORDER;


      then a trigger to watch the other schema's table and insert a record in my MONITOR_PERSONS table

      CREATE OR REPLACE TRIGGER PERSONS_AFTER_UPDATE
      AFTER UPDATE
      OF FAMILY_NAME
      ,FIRST_NAMES
      ON SWIFT_OWNER.PERSONS
      REFERENCING NEW AS NEW OLD AS OLD
      FOR EACH ROW

      BEGIN

      INSERT INTO SOMERSET.MONITOR_PERSONS

           (
           IDENTIFIER,
           OLD_FAMILY_NAME,
           OLD_FIRST_NAMES,
           NEW_FAMILY_NAME,
           NEW_FIRST_NAMES,
           UPDATE_DATE,
           UPDATE_STATUS,
           PERSONS_IDENTIFIER)

      VALUES

           (
           SOMERSET.MONITOR_PERSONS_SEQ.nextval,
           :OLD.FAMILY_NAME,
           :OLD.FIRST_NAMES,
           :NEW.FAMILY_NAME,
           :NEW.FIRST_NAMES,
           sysdate,
           'U',
           :OLD.IDENTIFIER);



      END PERSONS_AFTER_UPDATE;
      /


      Which compiles with this error

      18/11 PLS-00302: component 'MONITOR_PERSONS_SEQ' must be declared


      I have been around the net a number of times, and I just can't figure out why a sequence that exists in the same schema as the trigger is causing this issue.

      Can someone help please?
        • 1. Re: ORA-02289: sequence does not exist
          APC
          grant select on SOMERSET.MONITOR_PERSONS_SEQ to SWIFT_OWNER
          /
          Cheers, APC
          • 2. Re: ORA-02289: sequence does not exist
            Jens Petersen
            Have you been connected as SOMERSET or SWIFT_OWNER when you created the trigger?
            • 3. Re: ORA-02289: sequence does not exist
              torch
              I dropped all the objects and started again.

              This time I included the

              grant select on SOMERSET.MONITOR_PERSONS_SEQ to SWIFT_OWNER;

              statement.

              I still get the same error though.

              I created all the objects while connected with the SOMERSET account.

              As further investigation my end. I created another table in the SOMERSET schema called test_persons and another trigger like the first, but this time for SOMERSET.test_persons.

              I get the same error message for this trigger as well.

              18/11 PLS-00302: component 'MONITOR_PERSONS_SEQ' must be declared
              • 4. Re: ORA-02289: sequence does not exist
                507047
                Pls tell me if my perception on your explanation is wrong.

                You have to mornitor SWIFT_OWNER schema. For that you have rights to access SOMERSET schema. So you have created your own log table to track the log info.

                Pls check following approach.

                1. Create a table MONITOR_PERSONS in your SOMERSET schema and create
                a public synonym for that.
                2. Create a Sequence in SOMERSET schema and Create a public synonym for
                that Sequence
                3. Grant previliges with ALL option to the ROLE in SWIFT_OWNER on both the
                table and Sequence.
                4. Trigger should be created in SWIFT_OWNER schema, which will insert the
                data in MONITOR_PERSONS table.

                Since you are the owner of the table MONITOR_PERSONS, you will be able to see the data in your monitor table.

                Hope this should meet your requirement.

                Cheers
                Ram
                • 5. Re: ORA-02289: sequence does not exist
                  Arun Kumar Gupta
                  I have an schema called AIP. I executed following and it worked.


                  AIP@FBCTD1>--Table
                  AIP@FBCTD1>drop table AIP.MONITOR_PERSONS;

                  Table dropped.

                  Elapsed: 00:00:00.50
                  AIP@FBCTD1>
                  AIP@FBCTD1>CREATE TABLE AIP.MONITOR_PERSONS
                  2 (
                  3 IDENTIFIER INTEGER,
                  4 OLD_FAMILY_NAME VARCHAR2(40 BYTE),
                  5 OLD_FIRST_NAMES VARCHAR2(40 BYTE),
                  6 NEW_FAMILY_NAME VARCHAR2(40 BYTE),
                  7 NEW_FIRST_NAMES VARCHAR2(40 BYTE),
                  8 UPDATE_DATE DATE,
                  9 UPDATE_STATUS CHAR(1 CHAR),
                  10 PERSONS_IDENTIFIER INTEGER
                  11 );

                  Table created.

                  Elapsed: 00:00:00.48
                  AIP@FBCTD1>
                  AIP@FBCTD1>drop SEQUENCE AIP.MONITOR_PERSONS_SEQ ;

                  Sequence dropped.

                  Elapsed: 00:00:00.50
                  AIP@FBCTD1>
                  AIP@FBCTD1>-- Sequence
                  AIP@FBCTD1>CREATE SEQUENCE AIP.MONITOR_PERSONS_SEQ
                  2 START WITH 1
                  3 MAXVALUE 999999999999999999999999999
                  4 MINVALUE 1
                  5 NOCYCLE
                  6 NOCACHE
                  7 NOORDER;

                  Sequence created.

                  Elapsed: 00:00:00.46
                  AIP@FBCTD1>
                  AIP@FBCTD1>drop table AIP.TEST_PERSONS;
                  drop table AIP.TEST_PERSONS
                  *
                  ERROR at line 1:
                  ORA-00942: table or view does not exist


                  Elapsed: 00:00:00.71
                  AIP@FBCTD1>-- Table
                  AIP@FBCTD1>CREATE TABLE AIP.TEST_PERSONS
                  2 (
                  3 IDENTIFIER INTEGER,
                  4 FAMILY_NAME VARCHAR2(40 BYTE),
                  5 FIRST_NAMES VARCHAR2(40 BYTE)
                  6 );

                  Table created.

                  Elapsed: 00:00:00.48
                  AIP@FBCTD1>
                  AIP@FBCTD1>
                  AIP@FBCTD1>CREATE OR REPLACE TRIGGER PERSONS_AFTER_UPDATE
                  2 AFTER UPDATE
                  3 OF FAMILY_NAME
                  4 ,FIRST_NAMES
                  5 ON AIP.TEST_PERSONS
                  6 REFERENCING NEW AS NEW OLD AS OLD
                  7 FOR EACH ROW
                  8
                  9 BEGIN
                  10
                  11 INSERT INTO aip.MONITOR_PERSONS
                  12
                  13 (
                  14 IDENTIFIER,
                  15 OLD_FAMILY_NAME,
                  16 OLD_FIRST_NAMES,
                  17 NEW_FAMILY_NAME,
                  18 NEW_FIRST_NAMES,
                  19 UPDATE_DATE,
                  20 UPDATE_STATUS,
                  21 PERSONS_IDENTIFIER)
                  22 VALUES
                  23 (
                  24 AIP.MONITOR_PERSONS_SEQ.nextval,
                  25 :OLD.FAMILY_NAME,
                  26 :OLD.FIRST_NAMES,
                  27 :NEW.FAMILY_NAME,
                  28 :NEW.FIRST_NAMES,
                  29 sysdate,
                  30 'U',
                  31 :OLD.IDENTIFIER);
                  32 END PERSONS_AFTER_UPDATE;
                  33 /

                  Trigger created.

                  Elapsed: 00:00:00.51
                  AIP@FBCTD1>
                  AIP@FBCTD1>

                  Regards
                  Arun
                  • 6. Re: ORA-02289: sequence does not exist
                    557989
                    I wonder why you trying to provide value of AIP.MONITOR_PERSONS_SEQ.nextval in the insert statement. I beleive the better way is to create trigger for MONITOR_PERSONS table which assign this value inside of trigger. Here is an example:

                    -- login to AIP schema

                    -- create sequence MONITOR_PERSONS_SEQ

                    CREATE SEQUENCE SEQ_MONITOR_PERSONS
                    MINVALUE 1
                    NOCACHE
                    ORDER;

                    -- create trigger for MONITOR_PERSONS

                    CREATE OR REPLACE TRIGGER MONITOR_PERSONS_BI
                    BEFORE INSERT
                    ON MONITOR_PERSONS
                    FOR EACH ROW
                    BEGIN
                    select SEQ_MONITOR_PERSONS.NextVal into :New.Identifier from Dual;
                    END;
                    /

                    Now you can insert values into MONITOR_PERSONS without sequence value. It will be assigned automatically by trigger MONITOR_PERSONS_BI.

                    INSERT INTO SOMERSET.MONITOR_PERSONS

                    (
                    OLD_FAMILY_NAME,
                    OLD_FIRST_NAMES,
                    NEW_FAMILY_NAME,
                    NEW_FIRST_NAMES,
                    UPDATE_DATE,
                    UPDATE_STATUS,
                    PERSONS_IDENTIFIER)

                    VALUES

                    (
                    :OLD.FAMILY_NAME,
                    :OLD.FIRST_NAMES,
                    :NEW.FAMILY_NAME,
                    :NEW.FIRST_NAMES,
                    sysdate,
                    'U',
                    :OLD.IDENTIFIER);
                    • 7. Re: ORA-02289: sequence does not exist
                      Rob van Wijk
                      I beleive the better way is to create trigger for MONITOR_PERSONS table which assign this
                      value inside of trigger.
                      Really?
                      Time to change your belief:
                      SQL> create table mytable_without_trigger
                        2  ( id   number
                        3  , name varchar2(30)
                        4  )
                        5  /

                      Tabel is aangemaakt.

                      SQL> create table mytable_with_trigger
                        2  ( id   number
                        3  , name varchar2(30)
                        4  )
                        5  /

                      Tabel is aangemaakt.

                      SQL> create sequence myseq start with 1 increment by 1 nocache
                        2  /

                      Reeks is aangemaakt.

                      SQL> create or replace trigger my_trigger
                        2  before insert on mytable_with_trigger
                        3  for each row
                        4  begin
                        5    select myseq.nextval into :new.id from dual;
                        6  end;
                        7  /

                      Trigger is aangemaakt.

                      SQL> insert into mytable_without_trigger (id,name) select myseq.nextval, 'abc' from (select level from dual connect by level <= 100)
                        2  /

                      100 rijen zijn aangemaakt.

                      SQL> insert into mytable_with_trigger (name) select 'abc' from (select level from dual connect by level <= 100)
                        2  /

                      100 rijen zijn aangemaakt.

                      SQL> set timing on
                      SQL> insert into mytable_without_trigger (id,name) select myseq.nextval, 'abc' from (select level from dual connect by level <= 10000)
                        2  /

                      10000 rijen zijn aangemaakt.

                      Verstreken: 00:00:05.01
                      SQL> insert into mytable_with_trigger (name) select 'abc' from (select level from dual connect by level <= 10000)
                        2  /

                      10000 rijen zijn aangemaakt.

                      Verstreken: 00:00:07.02
                      SQL> set timing off
                      SQL> begin
                        2    runstats_pkg.rs_start;
                        3    insert into mytable_without_trigger (id,name) select myseq.nextval, 'abc' from (select level from dual connect by level <= 10000);
                        4    runstats_pkg.rs_middle;
                        5    insert into mytable_with_trigger (name) select 'abc' from (select level from dual connect by level <= 10000);
                        6    runstats_pkg.rs_stop(1000);
                        7  end;
                        8  /
                      Run1 draaide in 477 hsecs
                      Run2 draaide in 705 hsecs
                      Run1 draaide in 67,66% van de tijd

                      Naam                                                    Run1      Run2  Verschil
                      STAT.redo entries                                     20,151    30,092     9,941
                      LATCH.redo allocation                                 20,172    30,115     9,943
                      LATCH.shared pool                                     40,302    50,268     9,966
                      STAT.buffer is not pinned count                           67    10,067    10,000
                      STAT.execute count                                    10,012    20,012    10,000
                      STAT.table scan rows gotten                                1    10,001    10,000
                      STAT.table scans (short tables)                            1    10,001    10,000
                      STAT.table scan blocks gotten                              1    10,001    10,000
                      STAT.no work - consistent read gets                       41    10,041    10,000
                      STAT.recursive calls                                 140,126   150,127    10,001
                      STAT.db block gets                                    30,531    40,636    10,105
                      LATCH.library cache                                   90,571   110,439    19,868
                      LATCH.library cache pin                               80,326   100,214    19,888
                      STAT.db block changes                                 40,279    60,244    19,965
                      STAT.consistent gets                                  20,106    50,088    29,982
                      STAT.calls to get snapshot scn: kcmgss                30,018    60,018    30,000
                      STAT.session logical reads                            50,637    90,724    40,087
                      LATCH.cache buffers chains                           141,906   251,809   109,903
                      LATCH.session allocation                                   2   280,000   279,998
                      STAT.redo size                                     7,397,132 9,609,780 2,212,648

                      Run1 latches totaal versus run2 -- verschil en percentage
                            Run1      Run2  Verschil     Pct
                         584,676 1,034,688 450,012 56.51%

                      PL/SQL-procedure is geslaagd.
                      Regards,
                      Rob.

                      Message was edited by:
                      Rob van Wijk

                      Fixed typo
                      • 8. Re: ORA-02289: sequence does not exist
                        cd_2
                        Question is: would there be an improvement (in runtime) if the sequence generation would have been done through a function inside a package and the trigger only assigns the result of said function?

                        C.
                        • 9. Re: ORA-02289: sequence does not exist
                          Rob van Wijk
                          Don't know why that would be. But here is the test result:
                          SQL> create table mytable_without_trigger
                            2  ( id   number
                            3  , name varchar2(30)
                            4  )
                            5  /

                          Tabel is aangemaakt.

                          SQL> create table mytable_with_trigger
                            2  ( id   number
                            3  , name varchar2(30)
                            4  )
                            5  /

                          Tabel is aangemaakt.

                          SQL> create sequence myseq start with 1 increment by 1 nocache
                            2  /

                          Reeks is aangemaakt.

                          SQL> create package my_package
                            2  as
                            3    function get_new_id return number;
                            4  end;
                            5  /

                          Package is aangemaakt.

                          SQL> create package body my_package
                            2  as
                            3    function get_new_id return number
                            4    is
                            5      l_new_id number;
                            6    begin
                            7      select myseq.nextval into l_new_id from dual;
                            8      return l_new_id;
                            9    end;
                          10  end;
                          11  /

                          Package-body is aangemaakt.

                          SQL> create or replace trigger my_trigger
                            2  before insert on mytable_with_trigger
                            3  for each row
                            4  begin
                            5    :new.id := my_package.get_new_id;
                            6  end;
                            7  /

                          Trigger is aangemaakt.

                          SQL> insert into mytable_without_trigger (id,name) select myseq.nextval, 'abc' from (select level from dual connect by level <= 100)
                            2  /

                          100 rijen zijn aangemaakt.

                          SQL> insert into mytable_with_trigger (name) select 'abc' from (select level from dual connect by level <= 100)
                            2  /

                          100 rijen zijn aangemaakt.

                          SQL> set timing on
                          SQL> insert into mytable_without_trigger (id,name) select myseq.nextval, 'abc' from (select level from dual connect by level <= 10000)
                            2  /

                          10000 rijen zijn aangemaakt.

                          Verstreken: 00:00:05.09
                          SQL> insert into mytable_with_trigger (name) select 'abc' from (select level from dual connect by level <= 10000)
                            2  /

                          10000 rijen zijn aangemaakt.

                          Verstreken: 00:00:08.09
                          SQL> set timing off
                          SQL> begin
                            2    runstats_pkg.rs_start;
                            3    insert into mytable_without_trigger (id,name) select myseq.nextval, 'abc' from (select level from dual connect by level <= 10000);
                            4    runstats_pkg.rs_middle;
                            5    insert into mytable_with_trigger (name) select 'abc' from (select level from dual connect by level <= 10000);
                            6    runstats_pkg.rs_stop(1000);
                            7  end;
                            8  /
                          Run1 draaide in 514 hsecs
                          Run2 draaide in 848 hsecs
                          Run1 draaide in 60,61% van de tijd

                          Naam                                                    Run1      Run2  Verschil
                          STAT.session cursor cache hits                        10,002     8,157    -1,845
                          LATCH.enqueues                                            38     1,897     1,859
                          LATCH.session idle bit                                    62     1,979     1,917
                          LATCH.library cache pin allocation                    20,354    24,253     3,899
                          STAT.redo entries                                     20,151    30,094     9,943
                          STAT.buffer is not pinned count                           51    10,051    10,000
                          STAT.table scan blocks gotten                              1    10,001    10,000
                          STAT.table scans (short tables)                            1    10,001    10,000
                          STAT.table scan rows gotten                                1    10,001    10,000
                          STAT.no work - consistent read gets                       33    10,033    10,000
                          STAT.recursive calls                                 140,097   150,097    10,000
                          STAT.execute count                                    10,008    20,008    10,000
                          STAT.db block gets                                    30,514    40,630    10,116
                          LATCH.redo allocation                                 22,401    33,442    11,041
                          LATCH.shared pool                                     51,928    67,626    15,698
                          STAT.db block changes                                 40,263    60,235    19,972
                          STAT.consistent gets                                  20,100    50,084    29,984
                          STAT.calls to get snapshot scn: kcmgss                30,014    60,014    30,000
                          LATCH.library cache pin                               97,379   128,794    31,415
                          STAT.session logical reads                            50,614    90,714    40,100
                          LATCH.library cache                                  111,694   152,456    40,762
                          LATCH.cache buffers chains                           199,536   341,151   141,615
                          LATCH.session allocation                               2,168   286,896   284,728
                          STAT.redo size                                     7,397,124 9,772,492 2,375,368

                          Run1 latches totaal versus run2 -- verschil en percentage
                                Run1      Run2  Verschil     Pct
                             702,934 1,239,549   536,615  56.71%

                          PL/SQL-procedure is geslaagd.
                          Regards,
                          Rob.
                          • 10. Re: ORA-02289: sequence does not exist
                            cd_2
                            Don't know why that would be.
                            Because of Tom's article: Trigger v/s Procedure execution Speed (performance speed).

                            According to your test, it doesn't seem to make such a difference for a short function, thanks for doing that extra work.

                            C.
                            • 11. Re: ORA-02289: sequence does not exist
                              557989
                              Really?
                              Time to change your belief:
                              Faster doesn't always mean better.
                              • 12. Re: ORA-02289: sequence does not exist
                                torch
                                user504044, I followed your advice and it works fine. I tried updating the table and the trigger fired as expected.

                                This does leave me with a slight problem, I am not licensed to create objects in SWIFT_OWNER, but I am licensed to create objects in the SOMERSET schema.


                                Arun Kr Gupta, I have tried again to follow your advice but I still get the same error unfortunately. However if I log in using the SWIFT_OWNER account, I can create all the required objects in the SWIFT_OWNER schema and everything is created ok.

                                SWIFT_OWNER has lots of privileges including DBA rights, whereas SOMERSET has nine privileges and no DBA rights. The dba certainly would not want to give SOMERSET the same level of privileges, although they would be open to giving SOMERSET some more privileges if it helped get past this problem.

                                I appreciate I could be wrong, but it seems to me that somewhere SWIFT_OWNER has got the sort of rights that allows it to see pretty much everything in the database and therefore has no problem with the sequence.

                                I should point out, (with reference to my first post) that removing any reference to the sequence in the trigger (when the trigger is in the SOMERSET schema) also removes any problems. This sequence seems to be the real sticking point.

                                Thank you everyone for helping out so far.
                                • 13. Re: ORA-02289: sequence does not exist
                                  APC
                                  Really?
                                  Time to change your belief:
                                  Faster doesn't always mean better.
                                  Under what circumstances is this not the case? Eager minds want to know...

                                  Cheers, APC
                                  • 14. Re: ORA-02289: sequence does not exist
                                    6363
                                    Faster doesn't always mean better.
                                    Under what circumstances is this not the case?
                                    I am guessing under the circumstance that you only develop the software but don't have to actually use it to do your job.
                                    1 2 Previous Next