This discussion is archived
1 2 Previous Next 25 Replies Latest reply: Sep 4, 2013 1:59 PM by jgarry RSS

Cannot drop table

ravi.mann Newbie
Currently Being Moderated

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.... Oracle ACE
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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
    ravi.mann Newbie
    Currently Being Moderated

    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
    ravi.mann Newbie
    Currently Being Moderated

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

  • 5. Re: Cannot drop table
    ravi.mann Newbie
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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.... Oracle ACE
    Currently Being Moderated

    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
    ravi.mann Newbie
    Currently Being Moderated

    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.... Oracle ACE
    Currently Being Moderated

    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
    ravi.mann Newbie
    Currently Being Moderated

    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

  • 13. Re: Cannot drop table
    jgarry Guru
    Currently Being Moderated
  • 14. Re: Cannot drop table
    Aman.... Oracle ACE
    Currently Being Moderated

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

    ALL_TRIGGERS

     

    Aman....

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points