删除ogg用户表空间时告警ORA-00604、ORA-20782

denglj

问题描述：删除ogg用户表空间时告警ORA-00604、ORA-20782，如下所示：

数据库：oracle 11.2.0.4

1、异常重现

SYS@orcl> drop tablespace ogg_tbsincluding contents and datafiles;

drop tablespace ogg_tbs including contentsand datafiles

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQLlevel 2

ORA-20782: Oracle GoldenGate DDL ReplicationError: Code :ORA-20782: Cannot DROP object used in Oracle GoldenGatereplication while trigger is enabled. Consult Oracle GoldenGate documentationand/or

call Oracle GoldenGate Technical Support ifyou wish to do so., error stack: ORA-06512: at line 305

ORA-06512: at line 1266

2、异常原因

在安装OGG时配置开启过DDL捕获功能，OGG的DDL捕获依赖DDL触发器实现，DDL处于enabled状态，drop tablespace操作也属于DDL操作，所以产生ORA-00604 ORA-20782错误.

3、解决方案

--查触发器状态

SYS@orcl> col owner for a15

SYS@orcl> col trigger_name for a25

SYS@orcl> col triggering_event for a30

SYS@orcl> col status for a15

SYS@orcl> selectowner,trigger_name,trigger_type,triggering_event,status from dba_triggers wheretrigger_name like 'GGS%'

OWNER           TRIGGER_NAME              TRIGGER_TYPE     TRIGGERING_EVENT               STATUS

--------------- ----------------------------------------- ------------------------------ ---------------

SYS             GGS_DDL_TRIGGER_BEFORE    BEFORE EVENT     DDL                            ENABLED

SYS@orcl> drop triggerGGS_DDL_TRIGGER_BEFORE;

Trigger dropped.

--成功删除ogg表空间

SYS@orcl> drop tablespace ogg_tbsincluding contents and datafiles;

Tablespace dropped.