Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2004
    Posts
    8

    Problems dropping a user

    Hello

    I have a problem deleting a user from an Oracle 8.1.6 database. When I issue the command "drop user XYZ cascade" it returns with the following error:

    Code:
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-01418: specified index does not exist

    Using the web-based OracleTool shows that the user still has an index lying around:

    Code:
    Object name	Object type	Created	Last DDL time	Bytes	Extents
    SYS_C00371783 	INDEX 	December  08, 2003 - 15:11 	December  08, 2003 - 15:11 	         131,072 	               1

    How can I get rid of this object?

    It looks to me that a reference to an index is still there but the index itself is not. Don't ask me how that happened.

    Thanks for any hints.

    Sargon

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    what's the error message when dropping the index explicitly?

    which owner is linked to that index?

  3. #3
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    I assume you have tried to issue a "drop index SYS_C00371783" command

    I have had a problem a couple of times where the database doesn't seem to update it's catalogue properly, try creating a table and dropping it to see if that updates the catalogue.

    If there are still problems log on as that user and "select * from user_objects" to see what that lists
    There are 10 types of people in the world, those that know Binary and those that don't.

  4. #4
    Join Date
    Mar 2004
    Posts
    8
    I assume you have tried to issue a "drop index SYS_C00371783" command
    Yes I tried this and it answers with:

    Code:
    ORA-01418: specified index does not exist

    I have had a problem a couple of times where the database doesn't seem to update it's catalogue properly, try creating a table and dropping it to see if that updates the catalogue.
    Tried that too but it doesn't help. I created tables, indices ,constraints, filled data in them and deleted everything. But that last pesky object remains.


    If there are still problems log on as that user and "select * from user_objects" to see what that lists.
    When I do that I see the object again, that also the OracleTool saw:

    Code:
    OBJECT_NAME	SUBOBJECT_NAME   OBJECT_ID   DATA_OBJECT_ID   OBJECT_TYPE
    SYS_C00371783                    147114      147114           INDEX
    
    
    CREATED      LAST_DDL_T   TIMESTAMP             STATUS  T G S
    08.12.2003   08.12.2003   2003-12-08:15:11:44   VALID   N Y N

    When I try to delete it as this user (with "delete from user_objects where OBJECT_NAME='SYS_C00371783';") I get an "ORA-01031 insufficuent privileges" error.

    I guess there is a way to delete this object when logging in with system or sys?

    Thanks for your replies

    Sargon

  5. #5
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    are there any other objects in that users schema?

    clutching at straws but you could log on as that user and

    select 'drop '||object_type||' '||object_name||';' from user_objects

    then run that generated script in sqlplus
    There are 10 types of people in the world, those that know Binary and those that don't.

  6. #6
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482
    Originally posted by Sargon
    Yes I tried this and it answers with:

    Code:
    ORA-01418: specified index does not exist



    Tried that too but it doesn't help. I created tables, indices ,constraints, filled data in them and deleted everything. But that last pesky object remains.




    When I do that I see the object again, that also the OracleTool saw:

    Code:
    OBJECT_NAME	SUBOBJECT_NAME   OBJECT_ID   DATA_OBJECT_ID   OBJECT_TYPE
    SYS_C00371783                    147114      147114           INDEX
    
    
    CREATED      LAST_DDL_T   TIMESTAMP             STATUS  T G S
    08.12.2003   08.12.2003   2003-12-08:15:11:44   VALID   N Y N

    When I try to delete it as this user (with "delete from user_objects where OBJECT_NAME='SYS_C00371783';") I get an "ORA-01031 insufficuent privileges" error.

    I guess there is a way to delete this object when logging in with system or sys?

    Thanks for your replies

    Sargon
    "delete from user_objects where OBJECT_NAME='SYS_C00371783';" - never change the data dictionary this way!

    Try find out who is owner of this index or tablename that index belongs to and then:

    logon as owner of that index and simply drop that index.
    Or you can drop the table as sys.

    logon as sys or system and try following
    "SELECT owner, table_owner, table_name from dba_indexes where index_name = ''SYS_C00371783"

  7. #7
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482
    Originally posted by ika
    "delete from user_objects where OBJECT_NAME='SYS_C00371783';" - never change the data dictionary this way!

    Try find out who is owner of this index or tablename that index belongs to and then:

    logon as owner of that index and simply drop that index.
    Or you can drop the table as sys.

    logon as sys or system and try following
    "SELECT owner, table_owner, table_name from dba_indexes where index_name = ''SYS_C00371783"
    But this (SYS_C00371783) looks like constraint and not like index.
    log on as sys and try
    select * from dba_contraints where constraint_name = 'SYS_C00371783'
    or
    select * from dba_objects where object_name = 'SYS_C00371783'
    Last edited by ika; 04-13-04 at 07:07.

  8. #8
    Join Date
    Mar 2004
    Posts
    8
    Hello ika

    never change the data dictionary this way!
    Whoops, thanks for the warning!

    It is indeed a constraint. (so much for Oracle error messages *giggles*):

    Code:
    select owner, CONSTRAINT_NAME, TABLE_NAME from dba_constraints where CONSTRAINT_NAME='SYS_C00371783';
    
    OWNER      CONSTRAINT_NAME   TABLE_NAME
    ---------- ----------------- --------------
    XYZ        SYS_C00371783     INSTITUTES_OLD

    I logged in as XYZ and re-created the table "INSTITUTES_OLD" and deleted it again with "drop table INSTITUTES_OLD cascade constraints" but the thing is still there. (grr )



    The following statement as sys showed 12 constraints in total which are still there but shouldn't:

    Code:
    select CONSTRAINT_NAME, TABLE_NAME from dba_constraints where owner='XYZ';
    
    CONSTRAINT_NAME                TABLE_NAME
    ------------------------------ ------------------------------
    SYS_C00371772                  INSTITUTES_OLD
    SYS_C00371773                  INSTITUTES_OLD
    SYS_C00371774                  INSTITUTES_OLD
    SYS_C00371775                  INSTITUTES_OLD
    SYS_C00371776                  INSTITUTES_OLD
    SYS_C00371777                  INSTITUTES_OLD
    SYS_C00371778                  INSTITUTES_OLD
    SYS_C00371779                  INSTITUTES_OLD
    SYS_C00371780                  INSTITUTES_OLD
    SYS_C00371781                  INSTITUTES_OLD
    SYS_C00371782                  INSTITUTES_OLD
    
    CONSTRAINT_NAME                TABLE_NAME
    ------------------------------ ------------------------------
    SYS_C00371783                  INSTITUTES_OLD
    
    12 rows selected.

    Is it ok to delete them as sys with "delete from dba_constraints where owner='XYZ'"? I don't want to destroy yet another dictionary. I don't see a way to remove the 12 constraints otherwise. (I tried creating the table and then issuing "alter table institutes_old drop constraint SYS_C...." but it didn't work)

    Sargon

    PS. Thanks for all the help, guys

  9. #9
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482
    Originally posted by Sargon
    Hello ika


    Whoops, thanks for the warning!

    It is indeed a constraint. (so much for Oracle error messages *giggles*):

    Code:
    select owner, CONSTRAINT_NAME, TABLE_NAME from dba_constraints where CONSTRAINT_NAME='SYS_C00371783';
    
    OWNER      CONSTRAINT_NAME   TABLE_NAME
    ---------- ----------------- --------------
    XYZ        SYS_C00371783     INSTITUTES_OLD

    I logged in as XYZ and re-created the table "INSTITUTES_OLD" and deleted it again with "drop table INSTITUTES_OLD cascade constraints" but the thing is still there. (grr )



    The following statement as sys showed 12 constraints in total which are still there but shouldn't:

    Code:
    select CONSTRAINT_NAME, TABLE_NAME from dba_constraints where owner='XYZ';
    
    CONSTRAINT_NAME                TABLE_NAME
    ------------------------------ ------------------------------
    SYS_C00371772                  INSTITUTES_OLD
    SYS_C00371773                  INSTITUTES_OLD
    SYS_C00371774                  INSTITUTES_OLD
    SYS_C00371775                  INSTITUTES_OLD
    SYS_C00371776                  INSTITUTES_OLD
    SYS_C00371777                  INSTITUTES_OLD
    SYS_C00371778                  INSTITUTES_OLD
    SYS_C00371779                  INSTITUTES_OLD
    SYS_C00371780                  INSTITUTES_OLD
    SYS_C00371781                  INSTITUTES_OLD
    SYS_C00371782                  INSTITUTES_OLD
    
    CONSTRAINT_NAME                TABLE_NAME
    ------------------------------ ------------------------------
    SYS_C00371783                  INSTITUTES_OLD
    
    12 rows selected.

    Is it ok to delete them as sys with "delete from dba_constraints where owner='XYZ'"? I don't want to destroy yet another dictionary. I don't see a way to remove the 12 constraints otherwise. (I tried creating the table and then issuing "alter table institutes_old drop constraint SYS_C...." but it didn't work)

    Sargon

    PS. Thanks for all the help, guys
    <snip>
    alter table institutes_old drop constraint SYS_C...." but it didn't work
    </snip>
    what error message you got?

  10. #10
    Join Date
    Mar 2004
    Posts
    8
    <snip>
    alter table institutes_old drop constraint SYS_C...." but it didn't work
    </snip>
    what error message you got?
    That the constraint doesn't exist. Mean, huh?

    Code:
    ORA-02443: Cannot drop constraint  - nonexistent constraint
    So there's an entry for the constraint in the dba_constraints table, but when I want to delete it, it isn't there. Oh well, I guess I just ignore the user in the DB. It's not that's it's a production environment, so I can live with it. Thanks anyway.

    Sargon

  11. #11
    Join Date
    Dec 2009
    Posts
    1

    Lightbulb Data dictionary corrupted solution

    SELECT * FROM USER_OBJECTS
    -- 0 row found

    SELECT * FROM DBA_OBJECTS WHERE OWNER='TESTDB'
    -- 0 row found

    SQL> drop user TESTDB cascade;

    drop user TESTDB cascade

    ORA-00604: error occurred at recursive SQL level 1
    ORA-01418: specified index does not exist



    select * from user$
    --54 67 TESTDB

    select u.name, o.name, o.namespace, o.type#,
    decode(bitand(i.property,1024),0,0,1)
    from ind$ i,obj$ o,user$ u
    where o.obj#=i.bo# and o.owner#=u.user# and o.owner#='67'

    --HERE I WILL DELETE ALL THE ROWS OF THE OWNER 67
    select * from obj$ where owner#='67' for update


    SQL> drop user TESTDB cascade;

    User dropped

    SQL>


  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,627
    Post Operating System (OS) name & version for DB server system.
    Post results of
    SELECT * from v$version
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •