Dropping a Goldengate user schema - ORA-20782

Dropping an Oracle Goldengate user schema involves dropping Goldengate related triggers first before schema drop.
The reason being the error that we receive while trying to drop the schema.

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: Error stack is avalaible only on Oracle 10.1 and above
ORA-06512: at line 951

We have 2 options to drop the Oracle Goldengate user schema. Both options are presented below and is straight forward.

Option 1:

Run the below 3 scripts as SYSDBA user from Goldengate home directory with input as OGG schema name

@ddl_disable.sql
@ddl_remove.sql
@marker_remove.sql

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


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ cd $GGH
$ sqlplus  / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 6 06:42:11 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @ddl_disable.sql

Trigger altered.

SQL> @ ddl_remove.sql

DDL replication removal script.
WARNING: this script removes all DDL replication objects and data.

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.

Enter Oracle GoldenGate schema name:GGS_DBA
Working, please wait ...
Spooling to file ddl_remove_spool.txt

Script complete.
SQL> @marker_remove.sql

Marker removal script.
WARNING: this script removes all marker objects and data.

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.

Enter Oracle GoldenGate schema name:GGS_DBA

PL/SQL procedure successfully completed.


Sequence dropped.


Table dropped.


Script complete.
SQL>
SQL> drop user ggs_dba cascade;

User dropped.

Option 2:

Drop the trigger that is preventing the drop of schema and try to drop user.
Use the below query to find GGS user related trigger.

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;

Complete process is as below.

SQL> drop user GGS_DBA cascade;
drop user GGS_DBA 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: Error stack is avalaible only on Oracle 10.1 and above
ORA-06512: at line 951


SQL> select * from dba_triggers db where owner='GGS_DBA';

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;  2    3    4    5

      OBJ#   SYS_EVTS NAME
---------- ---------- ------------------------------
      5115        128 AW_DROP_TRG
      6261         16 SYS_LOGOFF
      6263          8 SYS_LOGON
      6265        128 NO_VM_DROP
      6267         32 NO_VM_CREATE
     39446     524256 GGS_DDL_TRIGGER_BEFORE

6 rows selected.

SQL> drop trigger GGS_DDL_TRIGGER_BEFORE;

Trigger dropped.

SQL> drop user GGS_DBA cascade;

User dropped.

Happy dropping!