Problems dropping a user

Sargon · #1 (**permalink**) 04-13-04, 05:24

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

osy45 · #2 (**permalink**) 04-13-04, 06:08

what's the error message when dropping the index explicitly?

which owner is linked to that index?

robert xr4x4 · #3 (**permalink**) 04-13-04, 06:10

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

Sargon · #4 (**permalink**) 04-13-04, 06:34

Quote:

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

Quote:

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.

Quote:

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

robert xr4x4 · #5 (**permalink**) 04-13-04, 06:42

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

ika · #6 (**permalink**) 04-13-04, 06:55

Quote:

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"

ika · #7 (**permalink**) 04-13-04, 06:59

Quote:

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'

Sargon · #8 (**permalink**) 04-13-04, 08:15

Hello ika

Quote:

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

ika · #9 (**permalink**) 04-13-04, 08:50

Quote:

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?

Sargon · #10 (**permalink**) 04-13-04, 09:32

Quote:

<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

apg360@gmail.com · #11 (**permalink**) 12-09-09, 09:55

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>

anacedent · #12 (**permalink**) 12-09-09, 10:15

Post Operating System (OS) name & version for DB server system.
Post results of
SELECT * from v$version

Internet Services

Web Development

Digital Marketing

Consumer Tech