Ghost index prevents DROP USER CASCADE
Jorge Martin-de-Nicolas
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
Anton Buijs
Jorge Martin-de-Nicolas <jorg...@yahoo.com> schreef in berichtnieuws
626a83ff.0307...@posting.google.com...
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.
Sybrand Bakker
"Jorge Martin-de-Nicolas" <jorg...@yahoo.com> wrote in message
news:626a83ff.0307...@posting.google.com...
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
Andy Hassall
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 (an...@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
Andy Hassall
<pos...@sybrandb.demon.nl> wrote:
>> -----------------------------------------------------
>> 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
>>
>> -----------------------------------------------------
>
>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.
If that were the case, he'd get 'ORA-02429: cannot drop index used for
enforcement of unique/primary key', not 'ORA-01418 specified index does not
exist'.
>You can drop an user by issuing
>drop user <username> cascade.
See above. That was the statement that initially caused the problem. You can't
drop the user when hitting this problem.
Anurag Varma
"Jorge Martin-de-Nicolas" <jorg...@yahoo.com> wrote in message news:626a83ff.0307...@posting.google.com...
> 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
Apart from what others have told you. If you can .. try bouncing the server and then try repeating the steps.
Also: You might want to contact Oracle Support first.
Anurag
Jorge Martin-de-Nicolas
$49115$e4fe...@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
Andy,
Thanks for the reply... Yep, you are right, I'm running an early
version of 8.1.7. It's not a critical database (just one I play
around with) so I won't open an Oracle TAR... that route is kind
of like pulling teeth :) Thanks for your post!
Jorge
Anton Buijs
Was afraid for that: looks your dictionary is corrupt. As mentioned in
another post there was a bug. I found it on Metalink (search for ORA 1418).
No solution for it.
Hope you have a support contract so you can open a TAR. Manually fixing the
dictionary can be a solution but must an can *only* be done with the
instructions provided by Oracle support.
I don't know how big or how important your database is, but the safest
option would be to make a full exp, create a new database and do a full imp.
That is: if the full exp still works. In this particular case check that you
have every object in the new database, the dictionary is corrput, you know.
Be carefull not to destroy your database before you have a proven to work
new database.
Good luck, looks like you need it.
Anton Buijs
Just forgot to ask: did you do something special? Did you had strange errors
in the alert.log. Do you have any idea what it was that you did that caused
this corruption? It is a very rare situation you know, dictionary
corruptions, and a big nightmare for DBA's.
When full exp fails, make a user exp of each user and see if you can build a
new db with these dumps.