Can't drop table that was just created

问题:

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 :)

回答1:

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.

回答2:

if you want to delete only the data of the table then you can use truncate.

Truncate TABLE [dbo].[table_name]

It will delete all the rows and If there is any autoincreament( or identity) column. then is seed is set to 1.