Oracle user deletion Error

Case 4: Oracle diagnosis using SQL _trace

Problem description: many times, when we perform database operations, such as drop user and drop table, we often encounter such errors.

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

This prompt is often useless. This case provides an idea and method for your reference.

1. drop user problems

Exit after the following error is reported

ORA-00604: error occurred at recursive SQL level 1

ORA-00942: table or view does not exist.

It is necessary to briefly describe recursive SQL errors.

We know that when we issue a simple command

Oracle databases need to parse this command in the background and convert it into a series of background operations for Oracle databases.

These background operations are collectively referred to as recursive SQL.

For example, a simple DDL command such as create table, Oracle database in the background, in fact, to convert this command to insert operations for the underlying table such as obj $, tab $, col $. Oracle's work may be much more complicated than we sometimes think.

2. Tracking Problems

We know that Oracle provides the SQL _trace function.

It can be used to track the background recursive operations of Oracle databases.

Through the tracking file, we can find the problem

The output after formatting (tkprof) is as follows:

The following statement encountered a error during parse:
Delete from SDO_GEOM_METADATA_TABLE WHERE SDO_OWNER = 'wapcomm'
Error encountered: ORA-00942

Oracle first presents the error information, and we see that the ORA-00942 error is caused by the SDO_GEOM_METADATA_TABLE/view does not exist, the problem can be located.

For this type of errors, the solution to the problem after the problem is located depends on the specific cause of the problem.

3. Problem locating

In this case, use Metalink to get the following explanation:

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:
Delete from SDO_GEOM_METADATA_TABLE WHERE SDO_OWNER =''
Solution Description

(1)

Create a synonym for SDO_GEOM_METADATA_TABLE under SYSTEM which points
MDSYS. SDO_GEOM_METADATA_TABLE.

In this example, you can create a synonym for MDSYS. SDO_GEOM_METADATA_TABLE, which is relatively simple.

(2)

Now the user can be dropped connected as SYSTEM.
Related Documents
ORA-604 and ORA-942 Reported During DROP USER CASCA

4. Actual handling

MDSYS. SDO_GEOM_METADATA_TABLE is a Spatial object.

If the Spatial option is not used, delete it.

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 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

In this case, the user can smoothly drop

This article is from Meng Zhiang's blog"