Jorge Martin-de-Nicolas <jorgemdn_at_yahoo.com> schreef in berichtnieuws
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
Post the results of select * from DBA_SEGMENTS where
segment_name='SYS_C00122756' and select * from DBA_INDEXES where
index_name='SYS_C00122756'
Also specify platform and Oracle version in your post.
Received on Wed Jul 16 2003 - 16:03:24 CDT
