案例分析之二-跟踪后台错误

问题描述

很多时候，在我们进行数据库操作时，比如drop user,drop table等，经常会遇到这样的错误：

ORA-00604: error occurred at recursive SQL level 1 .

单从这样的提示来看，很多时候是没有丝毫用处的，我们无法确定问题出在何处。本案例就这一类问题提供一个思路及方法供大家参考.

drop user出现问题

这是一个生产环境的数据库，在Drop User时报出以下错误后退出

ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist .

关于 recursive SQL 错误,我们有必要做个简单说明。当我们发出一条简单的SQL命令以后,Oracle数据库要在后台解析这条命令，并转换为Oracle数据库的一系列后台操作。这些后台操作统称为递归SQL。

比如create table这样一条简单的DDL命令，Oracle数据库在后台，实际上要把这个命令转换为对于obj$、tab$、col$等底层表的插入操作；对于drop table操作，则是在这些系统表中进行反向删除操作，大家同样可以通过sql_trace进行后台跟踪，进一步了解Oracle数据库的后台操作。Oracle所作的工作可能比我们有时候想的要复杂的多。

跟踪问题

通过Oracle提供sql_trace的功能，可以用于跟踪Oracle数据库的后台递归操作。研究跟踪文件，可以找到问题的所在，以下是这个问题的跟踪过程:

SQL> alter session set sql_trace=true;
Session altered.
SQL> drop user wapcomm;
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist .
SQL> alter session set sql_trace=false;

格式化(使用tkprof)跟踪文件后，我们获得以下输出(摘录部分):

********************************************************************************
The following statement encountered a error during parse:
DELETE FROM SDO_GEOM_METADATA_TABLE  WHERE SDO_OWNER = 'WAPCOMM'
Error encountered: ORA-00942
********************************************************************************
 
alter session set sql_trace=true
 
。。。。。。。。。。。。。。。
drop user wapcomm
 
………
 
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS
********************************************************************************
...........(省略部分递归SQL….)
 
********************************************************************************
 
delete from user_history$
where
 user# = :1             -----后台的递归删除操作…
 
……
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  DELETE USER_HISTORY$
      1   TABLE ACCESS FULL USER_HISTORY$
 
********************************************************************************
 
declare
   stmt varchar2(200);
BEGIN
     if dictionary_obj_type = 'USER' THEN
       stmt := 'DELETE FROM SDO_GEOM_METADATA_TABLE ' ||
               ' WHERE SDO_OWNER = ''' || dictionary_obj_name || ''' ';
       EXECUTE IMMEDIATE stmt;
    end if;
end;
 
。。。。。。。
 
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 31     (recursive depth: 1)
********************************************************************************
 
alter session set sql_trace=false
…………
 
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS

使用TKPROF格式化以后，Oracle把错误信息首先呈现出来。

我们看到ORA-00942错误是由于SDO_GEOM_METADATA_TABLE表/视图不存在所致,问题由此可以定位。对于这一类的错误，定位问题以后解决的方法就要依据具体问题原因而定了。

 问题定位

对于本案例，通过Metalink（http://metalink.oracle.com ）可以确认为一个Bug，获得以下解释和解决办法：

Problem Description
-------------------
The Oracle Spatial Option has been installed and you are encountering
the following errors while trying to drop a user, who has no spatial tables,
connected as SYSTEM:
 
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 7
 
A 942 error trace shows the failing SQL statement as:
 
DELETE FROM SDO_GEOM_METADATA_TABLE WHERE SDO_OWNER = '<user>'
 
Solution Description
--------------------
(1) Create a synonym for SDO_GEOM_METADATA_TABLE under SYSTEM which points to
MDSYS.SDO_GEOM_METADATA_TABLE.
(2) Now the user can be dropped connected as SYSTEM.
对于本例，为MDSYS.SDO_GEOM_METADATA_TABLE创建一个同义词即可解决.是相对简单的情况。MDSYS.SDO_GEOM_METADATA_TABLE为Spatial对象，如果未使用Spatial选项，可以删除
SQL> connect / as sysdba
Connected.
SQL> select * from dba_sdo_geom_metadata order by owner;
select * from dba_sdo_geom_metadata order by owner
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-04063: view "MDSYS.DBA_SDO_GEOM_METADATA" has errors
SQL> select object_name from dba_objects where object_name like '%SDO%';
OBJECT_NAME
--------------------------------------------------------------------------------
ALL_SDO_GEOM_METADATA
ALL_SDO_INDEX_INFO
ALL_SDO_INDEX_METADATA
DBA_SDO_GEOM_METADATA
DBA_SDO_INDEX_INFO
DBA_SDO_INDEX_METADATA
....
DBA_SDO_GEOM_METADATA
DBA_SDO_INDEX_INFO
...
88 rows selected.
SQL> drop user MDSYS cascade;
User dropped.
SQL> select owner,type_name from dba_types where type_name like 'SDO%';
no rows selected
SQL> alter session set sql_trace=true;
Session altered.
SQL> drop user wapcomm;
User dropped.
SQL> alter session set sql_trace=false;
Session altered.

这时用户得以顺利drop。 

一点总结

使用sql_trace可以跟踪数据库的很多后台操作，有利于我们发现问题的所在。

很多时候，我们想要研究Oracle的内部活动或后台操作,也可以通过sql_trace跟踪。这是深入研究学习Oracle的必经之途。