LINK:
http://www.eygle.com/case/SQL_TRACE_2.HTM
Problem Description:
Many times we are in the database operation, such as Drop User, Drop Table, etc., often encounter such errors.
ORA-00604: Error Occurred At Recursive Sql Level 1.
Such tips, many times there is no use. This case provides a thinking and approach for your reference on this type of problem.
1. DROP USER has issued a problem with the following error
ORA-00604: Error Occurred At Recursive Sql Level 1 ORA-00942: Table or View Does Not Exist.
About Recursive SQL errors We must be a brief description. We know, when we issue a simple command to resolve this command in the background, and convert to a series of background operations for Oracle Database. These background operations are called recursive SQL, such as Create Table, a simple DDL command Oracle database in the background, actually converts this command to the insertion of the underlying table of Obj $, Tab $, COL $. Oracle's work may be more thoughtful than us To be more complicated.
2. Tracking problems We know that Oracle provides SQL_Trace features to track the background recursive operation of the Oracle database. By tracking files, we can find the following is the format (TKPROF) Output:
*********************************************************** *****************************
The Following Statement Encountered A Error During Parse:
DELETE from SDO_GEOM_METADATA_TABLE WHERE SDO_OWNER = 'WAPCOMM'
Error Encountered: ORA-00942
*********************************************************** *****************************
Oracle first presented the error message we see that ORA-00942 error is due to SDO_GEOM_METADATA_TABLE Table / View does not exist, it can be positioned to locate errors for this class, and the method solution after positioning problems will be based on specific problems. .
3. Problem Positioning For this case, get the following explanation by Metalink: 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 ORA-06512: At Line 7 A 942 Error Trace Shows The Failing Sql Statement As: Delete from SDO_GEOM_METADATA_TABLE WHERE SDO_OWNER = '
SQL> Connect / as sysdbaconnected.
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 existORA-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_METADATAALL_SDO_INDEX_INFOALL_SDO_INDEX_METADATADBA_SDO_GEOM_METADATADBA_SDO_INDEX_INFODBA_SDO_INDEX_METADATA .... DBA_SDO_GEOM_METADATADBA_SDO_INDEX_INFO ... SDO_WITHIN_DISTANCEUSER_SDO_GEOM_METADATAUSER_SDO_INDEX_INFOUSER_SDO_INDEX_METADATA
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 success
SQL>
SQL> ALTER session set SQL_TRACE = true;
Session altered.
SQL> DROP USER WAPCOMM;
User Dropped.
SQL> ALTER session set SQL_TRACE = false;
Session altered.
SQL> EXITDISCONNECTED from Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Productionwith The Partitioning OptionjServer Release 8.1.7.4.0 - 64bit Production
At this time, the user can succeed DROP.
5. A little summary using SQL_Trace can track a lot of background operations that track the database. It is conducive to what we find problems. Many times we want to study Oracle's internal activities or background operations can also be used through SQL_TRACE tracking SQL_TRACE / 10046 is the most effective diagnosis provided by Oracle. One of the tools.
