GoldenGate - Error ORA-20782 while deleting Golden Gate User

Error ORA-20782 while deleting Golden Gate User

While dropping GoldenGate replicated user, I got an error.  Details are given below.

SQL> select distinct owner

     from dba_segments

     where owner in (select username

                from dba_users

                where default_tablespace not in ('SYSTEM','SYSAUX')

                ) ;

OWNER

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

GGTEST

OGGSOURCE

OGGTARGET

…

…

SQL> drop user GGTEST cascade;

drop user GGTEST cascade

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 2

ORA-20782: GoldenGate DDL Replication Error: Code :ORA-20782: Cannot DROP

object used in GoldenGate replication while trigger is enabled. Consult

GoldenGate documentation and/or call GoldenGate Technical Support if you wish to do so., error stack: ORA-06512: at line 231

ORA-06512: at line 957

Solution

Since I had run the ddl_setup.sql which created the DDL trigger for Golden Gate user schema.

So before deleting the Golden Gate replicated user, we need to drop the Golden Gate DDL trigger first.

SQL> SELECT * FROM DBA_TRIGGERS

WHERE OWNER='GGTEST';

no rows selected

SQL>  SELECT A.OBJ#, A.SYS_EVTS, B.NAME

FROM TRIGGER$ A,OBJ$ B

WHERE A.SYS_EVTS > 0

AND A.OBJ#=B.OBJ#

AND BASEOBJECT = 0;

      OBJ#   SYS_EVTS NAME

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

     11990     524256 LOGMNRGGC_TRIGGER

     13175       4096 AW_TRUNC_TRG

     13177       8192 AW_REN_TRG

     13179        128 AW_DROP_TRG

     13885       8416 NO_VM_DDL

     13886        128 NO_VM_DROP_A

     55420         64 CDC_ALTER_CTABLE_BEFORE

     55421         32 CDC_CREATE_CTABLE_AFTER

     55422         32 CDC_CREATE_CTABLE_BEFORE

     55423        128 CDC_DROP_CTABLE_BEFORE

     56322         96 EXPFIL_RESTRICT_TYPEEVOLVE

     56323       8256 EXPFIL_ALTEREXPTAB_MAINT

     57549       4224 XDB_PI_TRIG

     56320        128 EXPFIL_DROPOBJ_MAINT

     56321        128 EXPFIL_DROPUSR_MAINT

     58846       4096 RLMGR_TRUNCATE_MAINT

     63703        128 SDO_DROP_USER

     63971         32 SDO_ST_SYN_CREATE

     63807        128 SDO_TOPO_DROP_FTBL

     68065     524256 SDO_GEOR_BDDL_TRIGGER

     68066     524256 SDO_GEOR_ADDL_TRIGGER

     68143        128 SDO_NETWORK_DROP_USER

     71613          1 MGMT_STARTUP

     76915     524256 GGS_DDL_TRIGGER_BEFORE

24 rows selected.

SQL> drop trigger GGS_DDL_TRIGGER_BEFORE;

Trigger dropped.

SQL> drop user GGTEST cascade;

User dropped.