ORA-14452: attempt to create, alter or drop

一、概述

某数据库项目，早期用ogg软件同步数据，现在不需要了，需要删除ogg的表空间及ogg用户，释放空间。但是在删除ogg用户时遇到曲折，下面记录一下处理过程，以备后期查阅。

二、现象描述

2.1 故障现象

报Interesting stuff on dropping a table.

drop user GG cascade ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-14452: attempt to create, alter or drop an index on temporary table already in use

2.2 环境描述

• oracle版本：11.2.0.4

• 架构：rac

• ogg用户：GG

三、过程

step1: 查看被删除的用户有哪些对象

--query objects
SQL>  select owner,object_name,object_type from dba_objects where owner='GG';
​
OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ -------------------
OGG                            CLEAR_TRACE                    PROCEDURE
OGG                            CREATE_TRACE                   PROCEDURE
OGG                            DDLAUX                         PACKAGE
OGG                            DDLAUX                         PACKAGE BODY
OGG                            DDLORA_ERRORISUSERCANCEL       FUNCTION
OGG                            DDLORA_GETALLCOLSLOGGING       FUNCTION
OGG                            DDLORA_GETERRORSTACK           FUNCTION
OGG                            DDLORA_GETLOBS                 PROCEDURE
OGG                            DDLORA_VERIFYDDL               FUNCTION
OGG                            DDLREPLICATION                 PACKAGE
OGG                            DDLREPLICATION                 PACKAGE BODY
OGG                            DDLVERSIONSPECIFIC             PACKAGE
OGG                            FILE_SEPARATOR                 FUNCTION
OGG                            FILTERDDL                      FUNCTION
OGG                            GGS_STICK                      TABLE
OGG                            GGS_TEMP_COLS                  TABLE
OGG                            GGS_TEMP_UK                    TABLE
OGG                            INITIAL_SETUP                  PROCEDURE
OGG                            SYS_C0082969                   INDEX
OGG                            SYS_C0082971                   INDEX
OGG                            SYS_C0082973                   INDEX
OGG                            TRACE_PUT_LINE                 PROCEDURE

step2：手动逐个删除查询出来的对象

但是手动删除GGS_STICK对象时，报如下错误：

SQL> drop table GG_ADMIN_NP.GGS_STICK
            *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use
​
while digging further there are locks on the table, because of that reason it was not allowing you to drop the table. This query will get you the locks for that table.

step3：kill占用的相关会话

• 查询对象占用的会话

SELECT 'USER: '||s.username||' SID: '||s.sid||' SERIAL #: '||S.SERIAL# "USER HOLDING LOCK", s.inst_id
FROM gv$lock l
,dba_objects o
,gv$session s
WHERE l.id1 = o.object_id
AND s.sid = l.sid
AND o.owner = 'GG'
AND o.object_name = 'GGS_STICK';

输出内容如下：

USER HOLDING LOCK                                                                                                                        INST_ID
------------------------------------------------------------------------------------------------------------
USER: NC SID: 236 SERIAL #: 24203                                                                                                            1
USER: NC SID: 707 SERIAL #: 13051                                                                                                            1
USER: MDM SID: 236 SERIAL #: 49287                                                                                                             2
USER: MDM SID: 707 SERIAL #: 21287                                                                                                             2
USER: MDM SID: 881 SERIAL #: 56937 

• 根据查询出来的sid和 SERIAL，kill掉相关的会话

SQL> alter system kill session '195,1,@2';
​
System altered.

说明：

@2：@后面的数字表示实例id

 

step4：删除用户

SQL> drop user gg cascade;
​
User dropped.

 

四、引用

http://www.blogger.com/profile/06701478958409448996