Re: Ghost index prevents DROP USER CASCADE

From: Jorge Martin-de-Nicolas <jorgemdn_at_yahoo.com>
Date: 19 Jul 2003 10:56:45 -0700
Message-ID: <626a83ff.0307190956.1a0abe86@posting.google.com>

"Anton Buijs" <remove_aammbuijs_at_xs4all.nl> wrote in message news:<3f15bd82$0 $49115$e4fe514c_at_news.xs4all.nl>...

Hello Anton,

Thanks for your help. Here are the results you requested:

---

SQL> select * from DBA_INDEXES where index_name='SYS_C00122756';

no rows selected

---

SQL> select * from DBA_SEGMENTS where segment_name='SYS_C00122756';

OWNER

---

SEGMENT_NAME

---

PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ -----------------------------
HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS INITIAL_EXTENT

----------- ------------ ---------- ---------- ---------- -------------- NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS ----------- ----------- ----------- ------------ ---------- --------------- RELATIVE_FNO BUFFER_
------------ -------
TESTCOLO
SYS_C00122756

                               INDEX              USERS
          5         8946     131072         16          1         131072
     131072           1        4096            0          1               1
           5 DEFAULT



==============================================================================

>>Also specify platform and Oracle version in your post: >>
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production With the Partitioning option
JServer Release 8.1.7.0.0 - 64bit Production

uname -a
SunOS kalnayak 5.8 Generic_108528-16 sun4u sparc SUNW,Ultra-60

---

Thanks for your help!

Jorge

> 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 Sat Jul 19 2003 - 12:56:45 CDT