Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Ghost index prevents DROP USER CASCADE

Re: Ghost index prevents DROP USER CASCADE

From: Anton Buijs <remove_aammbuijs_at_xs4all.nl>
Date: Sat, 19 Jul 2003 20:37:52 +0200
Message-ID: <3f198fe0$0$49108$e4fe514c@news.xs4all.nl>

Jorge Martin-de-Nicolas <jorgemdn_at_yahoo.com> schreef in berichtnieuws 626a83ff.0307190956.1a0abe86_at_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.

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. Received on Sat Jul 19 2003 - 13:37:52 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US