DROP USER cascade on source db ,deadlock + ora 6508

故事发生平台：

oracle 11.2.0.2 +OGG 11.1.1.1 on windows 2008 64bit

前情提要

用户想删除源库某个需要复制的用户，且源库中配置了并开启了ogg 的ddl复制（为啥这里强调，因为在后面的解决方法中，正是因为某个开启的功能，导致这种错误结果的出现），从而 oracle alert 日志 中以每秒超过100条告警deadlock的速度出现如下报错：

MonMay 20 18:37:52 2013

ORA-00060:Deadlock detected. More info in fileD:\APP\ORACLE\diag\rdbms\prod\prod\trace\prod_ora_6148.trc.

ORA-00060:Deadlock detected. More info in fileD:\APP\ORACLE\diag\rdbms\prod\prod\trace\prod_ora_6148.trc.

ORA-00060:Deadlock detected. More info in fileD:\APP\ORACLE\diag\rdbms\prod\prod\trace\prod_ora_6148.trc.

MonMay 20 18:38:02 2013

ORA-00060:Deadlock detected. More info in fileD:\APP\ORACLE\diag\rdbms\prod\prod\trace\prod_ora_6148.trc.

prod_ora_6108.trc :
*** 2013-05-20 16:52:10.475
DEADLOCK DETECTED ( ORA-00060 )
 
[Transaction Deadlock]
 
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
 
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0007000d-00072770        34     392     X             34     392           S
 
session 392: DID 0001-0022-00000399 session 392: DID 0001-0022-00000399 
 
Rows waited on:
  Session 392: no row
 
----- Information for the OTHER waiting sessions -----
----- End of information for the OTHER waiting sessions -----
 
Information for THIS session:
 
----- Current SQL Statement for this session (sql_id=gxvb2kk0wumpb) -----
INSERT INTO "OGG" . "GGS_STICK" (PROPERTY, VALUE) VALUES ('lv_version', :B1 )
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
00000002F8C0F160      1132  package body OGG.DDLREPLICATION
00000002F9D16BA0       409  anonymous block
===================================================
PROCESS STATE
-------------
Process global information:
     process: 0x00000002F63421C8, call: 0x00000002F2EC4788, xact: 0x00000002F224D2C0, curses: 0x00000002F44EA940, usrses: 0x00000002F44EA940
     in_exception_handler: no
 

电视剧中插播”广告“，用户通过pl/sql dev 工具提示：

各位看官看出什么蛛丝马迹（此zsmj非某dota战队的主力），请把视线拉回报错图片上来，结合上述报错：

00000002F8C0F160      1132  package body OGG.DDLREPLICATION
00000002F9D16BA0       409  anonymous block

是不是有所启发？反派人物出场了对吧，ogg的ddl复制产生的lock，还tmd是 anonymous匿名的，ddlreplication 之前配置了什么，启用了什么？

既然你能看到这篇文章，多数人应该是奔着ogg来的，OGG在开ddl复制的最后一步是启用捕捉ddl操作的trigger

GGSCI>@DDL_ENABLE.SQL

#]more ddl_enable.sql 会发现：

....此处省略若干字....
@params 
- - enable DDL trigger

alter trigger sys.&ddl_trigger_name enable;
...

现在故事发展到这里，是不是看到几个很醒目的字：sys.ddl_trigger_name,来之与params.sql中定义的define ddl_trigger_name='GGS_DDL_TRIGGER_BEFORE'

那咱就看看这个反派角色到底干了啥？

通过select dbms_metadata.get_ddl('TRIGGER','SYS','GGS_DDL_TRIGGER_BEFORE') from dual;得到的ddl语句中有如下提示：

IF (upper(ora_dict_obj_name) = upper ('GGS_MARKER') OR                      
        upper(ora_dict_obj_name) = upper ('GGS_MARKER_SEQ') OR                  
        upper(ora_dict_obj_name) = upper ('GGS_DDL_TRIGGER_BEFORE') OR          
        upper(ora_dict_obj_name) = upper ('GGS_DDL_SEQ') OR                     
        upper(ora_dict_obj_name) = upper ('GGS_DDL_HIST') OR                    
        upper(ora_dict_obj_name) = upper ('GGS_DDL_HIST_ALT') OR                
        upper(ora_dict_obj_name) = upper ('GGS_SETUP')) AND                     
                                                                                
        upper(ora_dict_obj_owner) = upper ('OGG') AND                           
                                                                                
        (upper(ora_sysevent) = upper ('DROP') OR                                
         upper(ora_sysevent) = upper ('RENAME'))                                
                                                                                
        THEN                                                                    
        disallowDDL := 1;                                                       
        raise_application_error (-20782,                                        
                                 'Cannot DROP object used in Oracle GoldenGate r
eplication while trigger is enabled. ' ||                                       
                                 'Consult Oracle GoldenGate documentation and/or
 call Oracle GoldenGate Technical Support if you wish to do so.');              
    END IF;                    

在drop用户的时候，跟提示在红色字体部分由冲突，但是O记原厂老张同志说他以前实验中能删除，无解。