Drop user ORA-00942, ORA-00604, ORA-06512

[Aljaz]

Hello all!

When I want to drop user i get the following error :

SQL> drop user a;
drop user a
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-06512: at line 10

Database version
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production


Thank you for your help

Regards Aljaz

[xiaomao]

Run script $ORACLE_HOME/md/admin/catmd.sql as user MDSYS, then drop the user.

[Aljaz]

Thank you for your replay.

I did that, but the error is stil here.

Why must I install spatial option if I want to drop user????

That does not make sence. (as usually by Oracle)

Help still needed.

Regards
Aljaz

[akhadar]

The user which u are trying to drop has any objects in his schema.Also check are there any triggers which are preventing u from dropping the user.

[Aljaz]

User a is an account not schema.

I have enabled sql_trace and from what I can see there is a problem with XDB user.

When Oracle drop user it checks table xdb.xbd$schema, becouse this table does not exist the drop command fails.

It seems that I have to create XDB schema.

I have never manually installed XDB user and this is the production database so there is no room for mistakes.

Any expirances with install XDB schema.

Procedure for installing is :
SQL> CREATE TABLESPACE "XDB"
2 LOGGING DATAFILE '/u10/app/oradata/MYDB/xdb01.dbf' SIZE 50M REUSE
3 AUTOEXTEND ON NEXT 50M MAXSIZE 1500M
4 EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

SQL> @catqm.sql change_on_install XDB TEMP

SQL> @catxdbj.sql


Then you adjust dispatchers parameter.

And after that everything will be OK?
Right?

[Aljaz]

I had to create few synonyms for mdsys objects.

Now it is working.

Thank you all for your help.

[xiaomao]

Well, we can prove this by turn on the trace.

alter session set sql_trace=true;

drop user a;

In the tracefile, you should be able to see the statement like

DELETE FROM SDO_GEOM_METADATA_TABLE
WHERE
'"'||SDO_OWNER||'"' = '"A"'

You can check your database whether table SDO_GEOM_METADATA_TABLE exists or not.

SQL> select owner from dba_tables where table_name='SDO_GEOM_METADATA_TABLE';

OWNER
-----------------------
MDSYS

In your case, you are supposed to see some error messages like

ORA-00942: table or view does not exist
Current SQL statement for this session:
DELETE FROM SDO_GEOM_METADATA_TABLE WHERE '"'||SDO_OWNER||'"' = '"A"';

Here the error ORA-00942 refer to table SDO_GEOM_METADATA_TABLE. This is the reason why you need to install Oralce Spatial as the missing table is owned by MDSYS.

Make sure you install Oralce Spatial correctly and table SDO_GEOM_METADATA_TABLE
does exist in your database.

[Aljaz]

xiaomao thanks for your help.

It works fine.

Regards
Aljaz