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

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

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

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

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.

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?

I had to create few synonyms for mdsys objects.

Now it is working.

Thank you all for your help.

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.

xiaomao thanks for your help.

It works fine.

Regards
Aljaz