Can't drop table that was just created

Question

I created a table named dual2. I've a rows there, and can select from it. When attempting to drop it, it produces this error:

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

However, the table still exists! It returns from dba_tables and user_tables.

Any ideas on what's happening here??

alt text http://img180.imageshack.us/img180/6012/28140463.png

Here is the script of table creation, that i got with plsql developer:

-- Create table
create table
(
  DUMMY VARCHAR2(1)
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

P.S.: p.cambell thanks for editing! and sorry for my bad english :)

Yep, drop table system.dual2 does the same. And i logged as system. And the owner of table is system. — stee1rat
Which is why I don't think the table is in SYSTEM. I can't remember how to get the table script from PLSQL Developer - that would clear this up really quick. Right click on the table & select properties? — OMG Ponies
I have added a script that i got with plsql developer to the question text. But this table IS in the system schema.. — stee1rat
I wonder if you have some sort of problem with synonyms or other objects. What does "SELECT owner, object_name, object_type from dba_objects" give you? — dpbradley

Gary Myers Gary Myers · Accepted Answer · 2010-08-02T00:15:18

Rule 1 in NEVER create anything as system (or SYS). These are built-in schemas for built-in objects.

You'll probably have to connect as SYSDBA to have sufficient privileges to drop any objects owned by system. Also, depending on the install, there can be triggers that fire before a drop table (I think MDSYS has one) and which might not work for a SYSTEM object.

Personally, I'd be tempted to blow the database away and start again, or go back to a back from before you created the object.

Can't drop table that was just created

2 Answers