Drop goldengate用户时报ORA-00604 ORA-20782 ORA-06512问题解决

1、问题现象
SQL> drop user goldengate cascade; Drop goldengate用户时，报ORA-00604 ORA-20782 ORA-06512错误，具体报错内容如下：drop user goldengate cascade*ERROR at line 1:ORA-00604: error occurred at recursive SQL level 2ORA-20782: Oracle GoldenGate DDL Replication Error: Code :ORA-20782: CannotDROP object used in Oracle GoldenGate replication while trigger is enabled.Consult Oracle GoldenGate documentation and/or call Oracle GoldenGate TechnicalSupport if you wish to do so., error stack: ORA-06512: at line 261ORA-06512: at line 1111
2、原因分析
由于在安装OGG时，配置并开启了DDL捕获功能，而OGG的DDL捕获，是依赖DDL触发器实现的，DDL处于enabled状态，drop goldengate user操作也属于DDL操作，所以产生ORA-00604 ORA-20782错误3、验证DDL触发器状态
SQL> set linesize 999SQL>select owner,trigger_name,trigger_type,triggering_event,status from dba_triggers where trigger_name like "GGS%";OWNER TRIGGER_NAME TRIGGER_TYPE TRIGGERING_EVENT STATUS----------------- --------------------------- ----------------------- ----------------------------- ---------SYS GGS_DDL_TRIGGER_BEFORE BEFORE EVENT DDL ENABLED
4、删除触发器
SQL>drop trigger sys.GGS_DDL_TRIGGER_BEFORE5、再次尝试删除用户
SQL> drop user goldengate cascade;drop user goldengate cascade*ERROR at line 1:ORA-00604: error occurred at recursive SQL level 1ORA-14452: attempt to create, alter or drop an index on temporary table already in use
再次报错，但是报错内容发生了改变报错意思为：试图创建，更改或删除正在使用的临时表中的索引6、找出正在使用临时表的会话，并killsession
SQL>select "alter system kill session """||sid||","||serial#||"""" from v$session where sid in（select sid from v$lock where id1 in（select object_id from dba_objects where object_name in （select table_name from dba_tables where owner="GOLDENGATE"）））;7、再次删除用户
SQL> drop user goldengate cascade;User dropped.删除成功。更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址