Drop User with ORA-26500

Drop User with ORA-26500

When trying to Replicate the data from one machine to another machine, After getting latest dump from PROD, Before Import in process of cleanup all the schema using “Drop user” Occurred ORA-26500. This is much issue from Advanced Replication which is already enabled.

Advanced replication allows the simultaneous transfer of data between two or more Master Sites. Each Master Site is fully functional and doesn’t depend on pulling updates from a central site. In this example replication is setup between two Master Sites.

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8 Enterprise Edition Release 8.0.6.3.0 - Production PL/SQL Release 8.0.6.3.0 - Production CORE Version 4.0.6.0.0 - Production TNS for HPUX: Version 8.0.6.3.0 - Production NLSRTL Version3.3.3.0.0 - Production

Drop User with Cascade:-

SQL> drop user usd cascade; 
drop user usd cascade 
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-26500: error on caching "USD"."ALL_EXCEPTIONS"

Attempt to Drop Table of same Schema:-

SQL> drop table all_exceptions; 
drop table all_exceptions 
* 
ERROR at line 1: 
ORA-26500: error on caching "USD"."ALL_EXCEPTIONS"

Description of Error Code:-

ORA-26500: error on caching "string"."string"
Cause: Attempt to cache the replication information which is unavailable in the catalog for the object.
Action: Use dbms_reputil.sync_up_rep to validate the replication catalog, or use dbms_reputil.make_internal_pkg to validate internal package.

Workaround:-

Try to Remove the triggers by executing below package.

SQL> exec DBMS_REPUTIL.SYNC_UP_REP('USD','NODE');  
PL/SQL procedure successfully completed.  
SQL>

SQL> truncate table all_exceptions;
Table truncated.
SQL>

SQL> exec DBMS_REPUTIL.SYNC_UP_REP('USD','NODE');  
PL/SQL procedure successfully completed.  
SQL> drop user usd cascade;  
User dropped.  
SQL>

Hope This Helps. 🙂