8 Replies Latest reply: Feb 19, 2013 2:25 PM by 982153 RSS

    Cannot drop object?

    982153
      I am simply following the CBT nuggets fundamentals course and did the following:


      create sequence seq_int_by1
      INCREMENT BY 1 START WITH 1 MINVALUE 1;

      create sequence SEQ_testsequence
      increment by 100 start with 1000
      maxvalue 100000 minvalue 1000 cache 20;

      rename seq_testsequence to seq_junkme;

      drop sequence seq_junkme;

      Everything works except the last statement for which i get the following error:

      Error report:
      SQL Error: ORA-00604: error occurred at recursive SQL level 1
      ORA-20000: Cannot drop Object
      ORA-06512: at line 2
      00604. 00000 - "error occurred at recursive SQL level %s"
      *Cause:    An error occurred while processing a recursive SQL statement
      (a statement applying to internal dictionary tables).
      *Action:   If the situation described in the next error on the stack
      can be corrected, do so; otherwise contact Oracle Support.

      Any ideas why i cannot drop this sequence?

      Your help is greatly appreciated!
        • 1. Re: Cannot drop object?
          sb92075
          It works OK for me.
          SQL> create sequence seq_int_by1
          INCREMENT BY 1 START WITH 1 MINVALUE 1;
          
          create sequence SEQ_testsequence
          increment by 100 start with 1000
          maxvalue 100000 minvalue 1000 cache 20;
          
          rename seq_testsequence to seq_junkme;
          
          drop sequence seq_junkme;
            2  
          Sequence created.
          
          SQL> SQL>   2    3  
          Sequence created.
          
          SQL> SQL> 
          Table renamed.
          
          SQL> SQL> 
          Sequence dropped.
          
          SQL> 
          • 2. Re: Cannot drop object?
            vlethakula
            what is your db version?
            I am able to do (below on 10g)


            test1> create sequence seq_int_by1
            INCREMENT BY 1 START WITH 1 MINVALUE 1;

            create sequence SEQ_testsequence
            increment by 100 start with 1000
            maxvalue 100000 minvalue 1000 cache 20;

            rename seq_testsequence to seq_junkme; 2
            Sequence created.

            Elapsed: 00:00:00.01
            test1> GEM@ZBKTST1> 2 3
            Sequence created.

            Elapsed: 00:00:00.01

            test1> drop sequence seq_junkme;

            Sequence dropped.

            Elapsed: 00:00:00.01

            Edited by: vlethakula on Feb 18, 2013 7:37 AM
            • 3. Re: Cannot drop object?
              Aman....
              There shouldn't be any issue in dropping the sequence. Is it a test db? Did you try to restart the db and then do the drop of the sequence?

              Aman....
              • 4. Re: Cannot drop object?
                Richard Harrison .
                Hi,
                The fact you have an error code starting with 2 would imply its a user defined ddl trigger preventing the drop of the object

                ORA-20000: Cannot drop Object

                Did you create any ddl triggers earlier as part of your testing?

                You should drop/disable this trigger any try again.

                Cheers,
                Harry
                • 5. Re: Cannot drop object?
                  Osama_Mustafa
                  Error: ORA 604
                  Text: error occurred at recursive SQL level <num>
                  -------------------------------------------------------------------------------
                  Cause: An error occurred while processing a recursive SQL statement.
                  A recursive SQL statement is one that applies to internal dictionary
                  tables.
                  Action: If the situation described in the next message on the stack can be
                  corrected, do so; otherwise, contact customer support.
                  • 6. Re: Cannot drop object?
                    CKPT
                    drop sequence seq_junkme;

                    Everything works except the last statement for which i get the following error:

                    Error report:
                    SQL Error: ORA-00604: error occurred at recursive SQL level 1
                    ORA-20000: Cannot drop Object
                    ORA-06512: at line 2
                    Are you using normal user?
                    Hope you have privilege of dropping the object.

                    Tried using DBA account to drop the sequence as below?
                    SQL> drop sequence schema.object;
                    • 7. Re: Cannot drop object?
                      982153
                      Hey guys thanks for the replies, Richard i did create a trigger earlier on in the testing. How do i find out what triggers exist already to delete because i cant really remember the trigger.
                      • 8. Re: Cannot drop object?
                        982153
                        I found it, just had to go through my Oracle course notes and found what i had named it. Thank you. Funnily i had a trigger created to prevent users from dropping any objects in the HR schema, kinda explains it :o

                        Sorry to bother you guys. Richard Thank you very much forgot i did that