Ghost index prevents DROP USER CASCADE

From: Jorge Martin-de-Nicolas <jorgemdn_at_yahoo.com>
Date: 16 Jul 2003 13:48:40 -0700
Message-ID: <626a83ff.0307161248.9acdc46@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 Received on Wed Jul 16 2003 - 15:48:40 CDT