ORA-06564 While removing Oracle Streams

Try to remove Streams setting:

SQL> exec DBMS_STREAMS_ADM.remove_streams_configuration();

ORA-06564: object "MDS"."BIN$2m0kNCkdsv7gQKjAuL4Hww==$0" does not exist

ORA-06564 errors are misleading while removing oracle stream configuration using DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION().

When removing oracle stream some of objects are reference but not exist with original name.
It is due to referred objects are moved to recycle bin with new name with standard naming convention ‘BIN$==$0’.
When object moved to recycle bin then only name changed and not object_id of it causing DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION procedure to fail.
This is observed in Oracle 10g and 11g as well.

Solution

Purge the object from the recycle bin.

Login as owner, here I am using MDS as example:

# sqlplus MDS
PURGE TABLE "BIN$2m0kNCkdsv7gQKjAuL4Hww==$0";

Run again DBMS_STREAMS_ADM.remove_streams_configuration()
It may possible that package fails again for different object.

Remove recycle bin until clean:

PURGE TABLE "BIN$2m0kNCdpsv7gQKjAuL4Hww==$0";
PURGE TABLE "BIN$2m0kNCrksv7gQKjAuL4Hww==$0";
PURGE TABLE "BIN$2m0kNCeasv7gQKjAuL4Hww==$0";
PURGE TABLE "BIN$2m0kNCn+sv7gQKjAuL4Hww==$0";
PURGE TABLE "BIN$2m0kNCqZsv7gQKjAuL4Hww==$0";

Purge all objects of that schema OR purge dba_recyclebin using sysdba if you don’t need recovery of them.

# sqlplus / as sysdba
SQL> purge dba_recyclebin;

DBA Recyclebin purged.

SQL> exec DBMS_STREAMS_ADM.remove_streams_configuration();

PL/SQL procedure successfully completed.