views:

114

answers:

2

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

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

+7  A: 

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.

Gary
+1: Ah, that explains things...
OMG Ponies
I know about the Rule #1, but the problem that other users has this problem too, that's why i decided to do it as system or sys. For sys as sysdba it says 'insufficient privileges'. Of course, i can delete this db, but i just want to know - what's the problem.
stee1rat
Oh, and it's says 'insufficient privileges' for droping the other user's tables as sysdba too, btw.
stee1rat
Sounds like the problem is nothing to do with the actual table you've created, and some other vital SYSTEM or SYS table has been trashed somehow. Are there any invalid objects in the DB? If that is what's going on then rebuilding the DB, but might be (partly) fixable by rerunning catproc or similar. I'd export anything you want to keep and block access before touching anything else...
Alex Poole
You can try a DBMS_MONITOR trace and look through the trace file to see what triggers and/or recursive SQLs are being issued. It may be you can disable a trigger/drop the object/reenable the trigger.
Gary
A: 

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.

DineshHona
-1; a SQL Server answer for an Oracle problem. Oracle has neither autoincrement nor identity columns.
Adam Musch