Oracle Diagnostic Case-Sql_trace II

Question note:

Many times
When we do database operations
For example, drop user,drop table and so on, often encounter such errors.

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

Such prompts are often useless.
This case provides an idea and method for reference.

1. Dropuser has problems

Exit after reporting the following error

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

About recursive SQL errors
It is necessary for us to make a brief explanation.

We know that when we issue a simple command,
Oracle database parses this command in the background and converts it into a series of background operations of Oracle database.
These background operations are collectively referred to as recursive sql

A simple DDL command like create table
Oracle database is in the background, actually converting this command to
For the insertion operations of the underlying tables such as obj$,tab$,col $, etc.

Oracle's work may be more complex than we sometimes think.

2. Tracking problem

We know Oracle provides sql_trace functionality
It can be used to track the background recursive operation of Oracle database.

By tracking files, we can find the problem.
The following is the output after formatting (tkprof):

********************************************************************************
The following statement encountered a error during parse:
DELETE FROM SDO_GEOM_METADATA_TABLE WHERE SDO_OWNER = 'WAPCOMM' 
Error encountered: ORA-00942
********************************************************************************

Oracle presents error messages first
We see that the ORA-00942 error is due to the absence of SDO_GEOM_METADATA_TABLE tables/views.
The problem can thus be positioned.
For this kind of error, the method to solve the problem in the future depends on the specific reason of the problem.

 

3. Problem Location

For this case, the following explanation is obtained through 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 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. 

//For this example, create a synonym for MDSYS.SDO_GEOM_METADATA_TABLE to solve the problem.
//It's a relatively simple case.

(2) Now the user can be dropped connected as SYSTEM. 
Related Documents 
----------------- 
<Note.159776.1> ORA-604 and ORA-942 Reported During DROP USER CASCA

4. Practical treatment

MDSYS.SDO_GEOM_METADATA_TABLE is a Spatial object
If the Spatial option is not used, you can delete it

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
...
SDO_WITHIN_DISTANCE
USER_SDO_GEOM_METADATA
USER_SDO_INDEX_INFO
USER_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 selected

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> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production

At this point, the user can drop down smoothly.

5. A summary

Using sql_trace can track many background operations of the database, which is helpful for us to find the problem.

Very often, we want to study Oracle's internal activities or background operations, or we can track them through sql_trace.

sql_trace/10046 is one of the most effective diagnostic tools provided by Oracle.

Tags: SQL Oracle Database Session

Posted on Fri, 19 Apr 2019 20:18:33 -0400 by zenon