drop user xxx cascade无法成功,附初步诊断和跟踪文件

Alfred

环境：RHEL5.4 + Oracle 11.2.0.1
故障：drop user xxx cascade无法成功。
详细操作过程如下：


--1 删除用户报错信息：
drop user sde cascade;
drop user osl_gis cascade;

SQL> drop user sde cascade;
drop user sde cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-21700: object does not exist or is marked for delete


SQL> drop user osl_gis cascade;
drop user osl_gis cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

--2 尝试解决osl_gis删除问题
select username, sid, serial# from v$session where username='OSL_GIS';
目前OSL_GIS用户已经没有连接了，还是提示同样报错；ORA-01940: cannot drop a user that is currently connected
为了防止杀死会话还有新的连接，已经临时更改了要删除的用户密码为 123456

--3 删除sde用户过程的会话 跟踪文件
alter session set sql_trace=true;
drop user sde cascade;
alter session set sql_trace=false;
对应跟踪文件为：
/opt/oracle/diag/rdbms/irmsdb/irmsdb/trace/irmsdb_ora_21923.trc


--4 删除osl_gis用户过程的会话 跟踪文件
alter session set sql_trace=true;
drop user osl_gis cascade;
alter session set sql_trace=false;
对应跟踪文件为：
/opt/oracle/diag/rdbms/irmsdb/irmsdb/trace/irmsdb_ora_22566.trc


以上跟踪文件的下载地址：
链接: http://pan.baidu.com/s/1eS0qk98 密码: 1sb9

--5 跟踪文件初步分析
delete from sdo_geor_ddl__table$$ 这个删除在两个跟踪文件都有；
但是这个表不存在；
看命名像是空间组件的相关底层表，尝试重新初始化Oracle Spatial用到的表
@?/md/admin/catmd.sql
但结果依旧。

Liu Maclean(刘相兵

SDE 相关用户 如果有该问题一般很难drop ，可以考虑 重建库 或者忽略该用户的存在

Liu Maclean(刘相兵

Ora-21700 Object Does Not Exist Or Is Marked For Delete When Dropping a User With ST_GEOMETRY Dependencies (Doc ID 1385929.1)

APPLIES TO:

Oracle Spatial and Graph - Version 11.2.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 30-Oct-2014***
SYMPTOMS


When trying to drop a user, getting error

ORA-21700: object does not exist or is marked for delete

This is reproducible in ArcSDE environment
This is not reproducible with simple object types

ArcSDE has a data cartridge implementation in the schema called SDE
Schema SDE owns a type ST_GEOMETRY
This type is used in other schemas to create tables

How the corruption is introduced:
1. Try to drop user SDE using drop user cascade command: DROP USER SDE CASCADE;
This will generate error if there are objects created using SDE.ST_GEOMETRY because of dependencies

2. Try to drop the tables in other schemas using the SDE.ST_GEOMETRY type
Get also error ORA-21700: object does not exist or is marked for delete

3. Back to SDE and try to drop the SDE.ST_GEOMETRY type gives error
ORA-21700: object does not exist or is marked for delete

CAUSE


Dictionary gets corrupted when tables using a specific object type (SDE.ST_GEOMETRY) are tried to be dropped after the type was tried to get dropped

SOLUTION


=== ODM Solution / Action Plan ===

This is a known issue for ESRI (the creator of the cartridge that created the SDE.ST_GEOMETRY type)

http://support.esri.com/en/knowl ... ticles/detail/34483

Basically the objects cannot be dropped after this type of corruption is introduced

The objects can be left there and create a new database for this cartridge to be reinstalled or create a new database and do full exp/imp

In order to avoid this problem the objects using the SDE.ST_GEOMETRY type have to be dropped before dropping the SDE schema. Also the drop user SDE cascade should not be used. There are procedures explained in ESRI's website to remove the SDE cartridge. Please file a ticket with ESRI if you are going to remove users that use SDE.ST_GEOEMTRY type or the user SDE itself