问题：删除 ogg 用户的时候报错

问题描述

删除用户报错

SQL> drop user ogg cascade;
drop user ogg cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-20782: Oracle GoldenGate DDL Replication Error: Code :ORA-
20782: Cannot DROP object used in Oracle GoldenGate replication while trigger
is enabled.
Consult Oracle GoldenGate documentation and/or call Oracle
GoldenGate Technical
Support if you wish to do so., error stack: ORA-06512: at line
231
ORA-06512: at line 1030

原因分析

由于安装 OGG 时，配置并开启了 DDL 捕获功能，而 OGG 的 DLL 捕获，是依赖 DDL
触发器实现，DDL 处于 enabled 状态，drop ogg user 的操作也属于 DDL 操
作，所以产生 ORA-00604 和 ORA-20782 错误。

解决方案

删除对应的触发器就可以了。

查询 ddl 触发器

SQL>set linesize 999
SQL> col TRIGGERING_EVENT format a30
SQL> col status format a30
SQL> col TRIGGER_type format a30
SQL> col TRIGGER_NAME format a30
SQL> col owner format a20
SQL>select  owner,trigger_name,  trigger_type,
triggering_event,status from dba_triggers where trigger_name like 'GGS%';

删除触发器

SQL> drop trigger sys.GGS_DDL_TRIGGER_BEFORE;

再次尝试删除

SQL> drop user ogg cascade;
drop user ogg 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

还有错误，但是是另外一个错误，
错误：创建、修改或删除一个索引在临时表中的索引

查询正在使用临时表的会话，然后删除

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='OGG')));

再次删除

问题解决。