How to do a proper cleanup of Oracle Stream configuration for a fresh install of Oracle Streams:

How to do a proper cleanup of Oracle stream configuration for a fresh install of Oracle Streams:


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

EXEC DBMS_APPLY_ADM.STOP_APPLY (APPLY_NAME => 'APPLY_STREAM');

EXEC DBMS_CAPTURE_ADM.STOP_CAPTURE (CAPTURE_NAME => 'CAPTURE_STREAM');

begin
DBMS_PROPAGATION_ADM.STOP_PROPAGATION(PROPAGATION_name  => 'pdsmar_TO_nixar');
end;
/

exec DBMS_PROPAGATION_ADM.DROP_PROPAGATION(propagation_name => 'pdsmar_TO_nixar',drop_unused_rule_sets => TRUE);

EXEC DBMS_CAPTURE_ADM.DROP_CAPTURE(CAPTURE_NAME=>'CAPTURE_STREAM',drop_unused_rule_sets => TRUE);

EXEC DBMS_APPLY_ADM.DROP_APPLY(APPLY_NAME=>'APPLY_STREAM',drop_unused_rule_sets => TRUE);

connect / as sysdba

exec dbms_streams_adm.remove_queue(queue_name=>'STRMADMIN.APPLY_q',cascade=>true,drop_unused_queue_table=>true);

exec dbms_streams_adm.remove_queue(queue_name=>'STRMADMIN.CAPTURE_q',cascade=>true,drop_unused_queue_table=>true);


drop user STRMADMIN cascade;


----
If above steps  required repeated for multiple environments we can write something line tis:

 BEGIN

    DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION;

  EXCEPTION
   WHEN OTHERS THEN



     -- Drop the APPLY queue on Site1
     dbms_streams_adm.remove_queue(queue_name=>'STRMADMIN1.STREAMS_APPLY_Q1',cascade=>true,drop_unused_queue_table=>true);

     -- Drop the CAPTURE queue on Site1
     dbms_streams_adm.remove_queue(queue_name=>'STRMADMIN1.STREAMS_CAPTURE_Q1',cascade=>true,drop_unused_queue_table=>true);
  END;
/

drop user STRMADMIN cascade;