"Jorge Martin-de-Nicolas" <jorgemdn_at_yahoo.com> wrote in message
news:626a83ff.0307161248.9acdc46_at_posting.google.com...
> Hello Oracle DBAs and gurus,
>
> SUMMARY
> =======
> 1. I tried droping a user but failed with recursive SQL error
> 2. DROP USER CASCADE left a "ghost" index in USER_OBJECTS
> 3. I tried droping index manually but system can't find it
> 4. I'm in a Catch-22 situation... I can't drop the user
> because of the index, and I can't drop the index because
> the system can't find it
> 5. What can I try?
> 6. See sample screen shots below.
>
> FULL DESCRIPTION
> ================
> I tried droping a user but got a recursive SQL error as
> follows:
>
> -----------------------------------------------------
> SQL> drop user testcolo cascade;
> drop user testcolo cascade
> *
> ERROR at line 1:
> ORA-00604: error occurred at recursive SQL level 1
> ORA-01418: specified index does not exist
>
> -----------------------------------------------------
>
> The guilty index is shown below. It is the only object
> left in the "user_objects" table:
>
> -----------------------------------------------------
> SQL> select * from user_objects;
>
> OBJECT_NAME
> --------------------------------------------------------------------------
--
> SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
> ------------------------------ ---------- -------------- -----------------
-
> CREATED LAST_DDL_ TIMESTAMP STATUS T G S
> --------- --------- ------------------- ------- - - -
> SYS_C00122756
> 114790 114790 INDEX
> 16-JUL-03 16-JUL-03 2003-07-16:13:20:24 VALID N Y N
>
> -----------------------------------------------------
>
> I can't drop the index because the system can't find
> it. This is shown below:
>
> -----------------------------------------------------
> SQL> drop index SYS_C00122756;
> drop index SYS_C00122756
> *
> ERROR at line 1:
> ORA-01418: specified index does not exist
>
>
> -----------------------------------------------------
>
> QUESTION
> ========
> How can I drop the user? Should I log in as "sys" or
> "system" and try to manually delete the "ghost" index
> from the DBA_OBJECTS table?
>
> Any help would be appreciated,
>
> Thanks,
>
> Jorge
Looking at the name of the index, it is either a primary or an unique key
constraint. To drop those indexes, you'll need to drop the constraint by
using alter table drop constraint <constraint_name>. Dba_constraints will
show the associated table.
You can drop an user by issuing
drop user <username> cascade.
You can't be serious about directly deleting from dba_objects. If you want
to have a corrupt database on which Oracle will provide NO support, this is
the way to go.
--
Sybrand Bakker
Senior Oracle DBA
to reply remove '-verwijderdit' from my e-mail address
Received on Wed Jul 16 2003 - 16:05:56 CDT
