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