-
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?
Last edited by Aljaz; 09-08-2004 at 07:16 AM.
-
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.
Last edited by xiaomao; 09-08-2004 at 08:08 AM.
-
xiaomao thanks for your help.
It works fine.
Regards
Aljaz
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|