drop user a cascade;
drop user a cascade
*
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 11
今天遇到一则由于Oracle Spatial相关表不存在而导致个别用户无法删除的问题。比较有代表性,记录在此。
1.问题现象
在SYS用户下删除一个普通用户时抛出ORA-00604和ORA-00942错误。具体的报错信息即问题现象如下所示。
sys@secdb> drop user SEC_TARGET cascade;
drop user SEC_TARGET cascade
*
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 11
2.问题分析
由于该问题是由于执行一条SQL语句到导致,因此我们可以跟踪一下这个SQL语句的执行过程,希望可以得到一些蛛丝马迹。
1)启用SQL TRACE跟踪功能
sys@secdb> alter session set sql_trace=true;
Session altered.
sys@secdb> alter session set events'10046 trace name context forever,level 4';
Session altered.
2)再次执行报错的SQL语句
sys@secdb> drop user cascade;
sys@secdb> drop user SEC_TARGET cascade;
drop user SEC_TARGET cascade
*
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 11
3)关闭SQL TRACE跟踪功能
sys@secdb> alter session set sql_trace=false;
Session altered.
4)此时我们便可以在udump目录下找到SQL TRACE生成的对应的跟踪文件。
使用vi命令查看跟踪文件。截取部分信息如下。
secdb@bj-secdb1 /oracle/app/oracle/admin/secdb/udump$ vi secdb_ora_5711.trc
……
=====================
PARSING IN CURSOR #43 len=963 dep=1 uid=49 ct=47 lid=49 tim=1267560510536585 hv=560193968 ad='751138a8'
declare
stmt varchar2(200);
BEGIN
if dictionary_obj_type = 'USER' THEN
stmt := 'DELETE FROM SDO_GEOM_METADATA_TABLE ' ||
' WHERE SDO_OWNER = :owner ';
EXECUTE IMMEDIATE stmt USING dictionary_obj_name;
stmt := 'DELETE FROM SDO_MAPS_TABLE ' ||
' WHERE SDO_OWNER = :owner ';
EXECUTE IMMEDIATE stmt USING dictionary_obj_name;
stmt := 'DELETE FROM SDO_STYLES_TABLE ' ||
' WHERE SDO_OWNER = :owner ';
EXECUTE IMMEDIATE stmt USING dictionary_obj_name;
stmt := 'DELETE FROM SDO_THEMES_TABLE ' ||
' WHERE SDO_OWNER = :owner ';
EXECUTE IMMEDIATE stmt USING dictionary_obj_name;
stmt := 'DELETE FROM SDO_LRS_METADATA_TABLE ' ||
' WHERE SDO_OWNER = :owner ';
EXECUTE IMMEDIATE stmt USING dictionary_obj_name;
stmt := 'DELETE FROM SDO_TOPO_METADATA_TABLE ' ||
' WHERE SDO_OWNER = :owner ';
EXECUTE IMMEDIATE stmt USING dictionary_obj_name;
end if;
end;
END OF STMT
PARSE #43:c=0,e=34,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1267560510536583
BINDS #43:
=====================
PARSING IN CURSOR #39 len=62 dep=2 uid=49 ct=7 lid=49 tim=1267560510536996 hv=1927404519 ad='75113308'
DELETE FROM SDO_GEOM_METADATA_TABLE WHERE SDO_OWNER = :owner
END OF STMT
PARSE #39:c=0,e=189,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,tim=1267560510536992
BINDS #39:
kkscoacd
Bind#0
acdty=01 mxl=32(13) mxlc=00 mal=00 scl=00 pre=00
acflg=13 fl2=206001 frm=01 csi=31 siz=32 ff=0
kxsbbbfp=2b8dc5fe6568 bln=32 avl=13 flg=09
value="SEC_TARGET"
EXEC #39:c=1000,e=986,p=0,cr=1,cu=0,mis=1,r=0,dep=2,og=1,tim=1267560510538028
=====================
PARSING IN CURSOR #42 len=53 dep=2 uid=49 ct=7 lid=49 tim=1267560510538223 hv=1486718551 ad='7510cc48'
DELETE FROM SDO_MAPS_TABLE WHERE SDO_OWNER = :owner
……
TRACE跟踪信息显式的很清晰,在执行删除用户的过程中运行了几条DELETE删除语句,问题便出现在这里。
经确认其中提到的表SDO_GEOM_METADATA_TABLE数据库中已不存在。这便是报错的根本原因。表SDO_GEOM_METADATA_TABLE是Oracle Spatial用到的表。
3.处理方法
既然知道了导致用户无法删除的原因是由于找不到表SDO_GEOM_METADATA_TABLE所致。那处理方法便是找回之。
我们可以考虑使用catmd.sql脚本重新初始化Oracle Spatial用到的表的方法进行恢复。
1)catmd.sql脚本所在目录
$ORACLE_HOME/md/admin
2)使用SYS用户运行catmd.sql脚本
sys@secdb> @?/md/admin/catmd.sql
……此处省略大量运行信息……
3)在此尝试用户删除
sys@secdb> drop user SEC_TARGET cascade;
User dropped.
OK,用户删除成功。
4.小结
本文展示了一则由于Oracle Spatial相关表不存在而导致个别用户无法删除的问题。
这则问题给我们的启示是:
1)当出现因SQL执行相关的故障时可以考虑结合SQL TRACE功能进行问题分析和处理;
2)任何表面看上去不可思议的现象一定有其不为人知的原因;
3)往往问题原因与问题现象本身有较大出入;
4)作为DBA要对数据中的每个用户了然于心、了然于口、了然于手!
from:http://space.itpub.net/519536/viewspace-689469
//
alter session set sql_trace=true;
alter session set events '942 trace name errorstack context forever,level 10';
drop user accr cascade;
alter session set events '942 trace name errorstack contextoff';
alter session set sql_trace=false;
原帖由 gkl0818 于 2009-1-12 10:46 发表
942是什么事件??
ORA-00942: 表或视图不存在
from:http://www.itpub.net/thread-1112549-1-1.html
/
刚好有个现成的例子. 很多年前的了.
环境描述:
操作系统 windowxp .数据库 oracle 9.0.2.1 升级到oracle 9.0.2.7
问题描述:
删除用户 报ora-00604错误
例子如下:
sql>drop user cms53
第 1 行出现错误:
ORA-00604: 递归 SQL 层 1 出现错误
ORA-00942: 表或视图不存在
ORA-06512: 在line 1
解决过程.
1.做10046 trace跟踪。(oracle 跟踪事件太多不好一一说明。详细见<<oracle跟踪事件帖>>。)
SQL>alter session set events '10046 trace name context forever,level 12';(此设置对所有用户的所有进程生效、包括后台进程进行跟踪。.)
SQL> drop user cms53 cascade;
SQL>alter session set events '10046 trace name context off';( 跟踪完了一定记得关闭跟踪。不然非常耗数据库资源,对数据库有性能负面影响);
说明:跟踪完成后,数据库生成一个*.trc文件。看生成时间。应该是你开始跟踪的时间。目录为
$oracle_home\admin\$oracle_sid\udump\*.trc。
2.tkprof trace文件
tkprof 说明:
(1)tkprof 是oracle 自带的一种文件格式化工具,tkprof是用来解释trace文件内容,把原始的trace文件转化为容易理解的文件。
(2)使用方法:在操作系统命令行下直接敲:tkprof.你能看到他的详细说明。
使用方法格式:tkprof trace文件名 报告文件名
举例:
c:\>tkprof $oracle_home\admin\testdb\udump\oracledb_ora_3340.trc c:\cms5_droptable.txt
3.查看错误原因(格式化后文件内容会很大,你只需要搜索错误信息就可以了。).如下就是我搜索的到错误信息,):
The following statements encountered a error during parse:
select rowid from XDB.XDB$SCHEMA s where s.xmldata.schema_url = :1
Error encountered: ORA-00942
--------------------------------------------------------------------------------
DELETE FROM SDO_MAPS_TABLE WHERE SDO_OWNER = 'CMS53'
Error encountered: ORA-00942
低版本的BUG
到这里,错误信息已经非常明确了。就是因为用户mdsys模式下的两个表SDO_MAPS_TABLE,XDB.XDB$SCHEMA不存在造成的。解决办法一般有两个。一 :重建这两个表。二:删除mdsys用户。
在这里我删除了这个用户及用户所有对象。因为这个数据库默认用户删除对数据库使用没有影响。
4.由于MDSYS用户的两个表造成不能删除.删除MDSYS用户(此用户为可删除系统用户.)
sql> drop user mdsys cascade.
5.删除了mdsys用户之后就可以对其他创建的用户进行删除了.
6.问题得以解决.
7:其他说明:数据库相同的错误可能造成的原因千变万化。千万别死套模式。学会变通。
相同的错误造成的原因可能不同解决办法也不一样。我把我遇到另一个原因贴在下面。
提供大家参考。
OBJECT_TYPE OBJECT_NAME STATUS
-------------------- ------------------------------ ---------------
RULE AQ$WF_DEFERRED_QUEUE_M$1 VALID
RULE SET AQ$WF_DEFERRED_QUEUE_M$1 INVALID
1.Set the following event at session level:
alter session set events '25475 trace name context forever, level 2';
2. Drop rule:
execute DBMS_RULE_ADM.DROP_RULE('.AQ$WF_DEFERRED_QUEUE_M$1',TRUE);
commit;
3.Drop rule set :
execute DBMS_RULE_ADM.DROP_RULE_SET('AQ$WF_DEFERRED_QUEUE_M$1');
commit;
4. Connect as SYSTEM or SYSDBA and try to drop user again.
drop user cascade;
这是由于rule造成的ora-00604的错误。解决方法。