Oracle Diagnostics Case-sql

Oracle
Link

Http://www.eygle.com/case/sql_trace_2.htm



Problem Description:

Many times
When we do database operations
such as drop User,drop table and so on, often encounter such a mistake


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

Such hints, many times, do not have the slightest use.
This case provides a thought and method for reference in this kind of problem.

1. Problem occurred with drop user

Exit after reporting the following error




Ora-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 send out a simple command
The Oracle database will parse this command in the background and convert to a series of background operations for the Oracle database.
These background operations are collectively referred to as recursive SQL

such as CREATE TABLE, a simple DDL command
The Oracle database is in the background, and the command is actually converted to
Insert operation for the underlying table such as obj$,tab$,col$.

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


2. Tracking issues

We know that Oracle provides SQL_TRACE functionality
Can be used to track background recursive operations on Oracle databases.

By tracking the file, we can find out where the problem is.
The following is the output after the format (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 the error message first.
We see the ORA-00942 error because the Sdo_geom_metadata_table table/view does not exist

This is where the problem can be fixed.

For this kind of mistake, the solution of locating problem will be decided according to the specific problem reason.



3. Problem orientation

For this case, the following explanations are 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, creating a synonym for mdsys.sdo_geom_metadata_table can be resolved.
is a relatively simple situation.

(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. Actual processing

Mdsys. Sdo_geom_metadata_table as Spatial object
If you do not use the spatial option, you can delete








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

Selected rows.

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




Then the user is able to drop it smoothly



5. A little summary

Use Sql_trace to track many background operations for a database
To help us find out where the problem is.

Many times, we want to study Oracle's internal activities or background operations
You can also track by Sql_trace

sql_trace/10046 is one of the most effective diagnostic tools available to Oracle.