1 2 Previous Next 25 Replies Latest reply: Sep 4, 2013 3:59 PM by jgarry RSS

    Cannot drop table

    R Mann

      Versions are Oracle 11.2.0.1.0 and SQL Developer 4.0.0.12 on Windows 7 Ultimate SP1.

       

      Hi

       

      I'm following the CBT Nuggets SQL Fundementals training (video #11) and cannot drop a table I have just created.  The command executed and error are:

       

      drop table newprods;

       

      Error starting at line : 1 in command -

      drop table newprods

      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.

       

      As the HR user I created two tables and created a FK constraint between them.  After truncating the table with this FK, I am unable to drop it.  Even if I remove the FK, the error is the same.  Issing the command in SQL*Plus gives the same error.

       

      This is the first time I have created any tables since installing Oracle on this machine and is my first attempt at dropping a table.  I have not created any sequences, triggers or views based on these newly created tables.

       

      Does anyone have any ideas?

       

      Cheers

        • 1. Re: Cannot drop table
          Aman....

          Can you show the steps that you have used , the exact create table and drop table statements, preferably copied from the sqlplus screen?

           

          Aman....

          • 2. Re: Cannot drop table
            TSharma-Oracle

            Do you have any trigger on this table? If so, can you drop or disable the trigger and then try dropping the table.

            • 3. Re: Cannot drop table
              R Mann

              I created the tables in SQL Developer using right click > New Table..., so there is no create table code, it was all done via the GUI.  Attempted to drop:

               

              drop table newprods;

               

              Table was created successfully as I added data via the GUI and was able to successsfully run:

               

              select * from newprods;

              • 4. Re: Cannot drop table
                R Mann

                No triggers, sequences or views as I stated.  None created by me anyway or any code I have run.

                • 5. Re: Cannot drop table
                  R Mann

                  I was able to drop the table as the SYS user, but the error I got as the HR user is just not very helpful.

                   

                  As HR user I can create a table, but I should also be able to drop it, right?

                  • 6. Re: Cannot drop table
                    UserAL1178M

                    One reason may be that your table is locked. I would suggest you to check if any lock exist in your table.

                    • 7. Re: Cannot drop table
                      TSharma-Oracle

                      didnt read your question carefully, didnt thin k you are just practising. Issue seems to be with sql developer tool. What version you have? See bug

                      • 7243750: Drop table purge

                       

                       

                      http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/bugsfixed-v155-087945.html

                      • 8. Re: Cannot drop table
                        jgarry

                        Right.  I'd ask this in the sql developer forum.  I suspect you didn't create it as the HR user, some tool specific issue.

                         

                        Maybe you should try it again, and see what dba_tables says about ownership.

                        • 9. Re: Cannot drop table
                          Aman....

                          Doesn't seem like a normal thing and I  think it has something to do with sql developer itself. How about you do the same using just the sql*plus tool?

                           

                          Aman....

                          • 10. Re: Cannot drop table
                            R Mann

                            I also thought it might be SQL Developer, but I have just executed the following in SQL*Plus:

                             

                            SQL> show user

                            USER is "HR"

                            SQL> CREATE TABLE table1 (column1 VARCHAR2(20 BYTE));

                             

                            Table created.

                             

                            SQL> drop table table1;

                            drop table table1

                            *

                            ERROR at line 1:

                            ORA-00604: error occurred at recursive SQL level 1

                            ORA-20000: Cannot drop object

                            ORA-06512: at line 2

                             

                            So the same error.  It looks like the problem is not confined to SQL Developer.

                             

                            Maybe HR can create tables but not drop them?  I will see if I can find this out.

                            • 11. Re: Cannot drop table
                              Aman....

                              ravi.mann wrote:

                               

                              I also thought it might be SQL Developer, but I have just executed the following in SQL*Plus:

                               

                              SQL> show user

                              USER is "HR"

                              SQL> CREATE TABLE table1 (column1 VARCHAR2(20 BYTE));

                               

                              Table created.

                               

                              SQL> drop table table1;

                              drop table table1

                              *

                              ERROR at line 1:

                              ORA-00604: error occurred at recursive SQL level 1

                              ORA-20000: Cannot drop object

                              ORA-06512: at line 2

                               

                              So the same error.  It looks like the problem is not confined to SQL Developer.

                               

                              Maybe HR can create tables but not drop them?  I will see if I can find this out.

                              No such thing that HR can't drop its own tables. Any owner of any object can do whatever is possible for that object. Show us that you have connected to the HR, created a table and then tried to drop it and also confirm that you have got no triggers available.

                               

                              Aman....

                              • 12. Re: Cannot drop table
                                R Mann

                                C:\Oracle>sqlplus hr@orcl

                                 

                                 

                                SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 4 18:58:55 2013

                                 

                                 

                                Copyright (c) 1982, 2010, Oracle.  All rights reserved.

                                 

                                 

                                Enter password:

                                 

                                 

                                Connected to:

                                Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

                                With the Partitioning, OLAP, Data Mining and Real Application Testing options

                                 

                                 

                                SQL> CREATE TABLE table1 (column1 VARCHAR2(20 BYTE));

                                 

                                 

                                Table created.

                                 

                                 

                                SQL> select * from table1;

                                 

                                 

                                no rows selected

                                 

                                 

                                SQL> drop table table1;

                                drop table table1

                                *

                                ERROR at line 1:

                                ORA-00604: error occurred at recursive SQL level 1

                                ORA-20000: Cannot drop object

                                ORA-06512: at line 2

                                 

                                 

                                 

                                SQL>

                                 

                                 

                                Can I run a query to see if there are any triggeres on the table?

                                 

                                EDIT: Ok it looks like no triggers:

                                 

                                SQL> show user

                                USER is "SYS"

                                SQL> select * from DBA_TRIGGERS where table_name like '%table1%';

                                 

                                no rows selected

                                 

                                SQL> select * from USER_TRIGGERS where table_name like '%table1%';

                                 

                                no rows selected

                                • 14. Re: Cannot drop table
                                  Aman....

                                  The error looks like a user-defined string. You can use the view USER_TRIGGERS .

                                  ALL_TRIGGERS

                                   

                                  Aman....

                                  1 2 Previous Next