Re: Ghost index prevents DROP USER CASCADE

On 16 Jul 2003 13:48:40 -0700, jorgemdn_at_yahoo.com (Jorge Martin-de-Nicolas) wrote:

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

 There is an Oracle bug that causes these symptoms, I've seen it on an early version of 8.1.7.

 Haven't got the bug number handy at the moment, or whether it was fixed in a later patchset; if it's an important database then raise a TAR with Oracle.

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)