Connect boris_subscriber/boris_subscriber@whatever
-- Drop synonyms and CDC views that may be active at this time
/* Login as a subscriber to run this */
BEGIN
	DECLARE vSubhandle NUMBER;
		v_subscription_description VARCHAR2(40):='scott -> Datawarehouse';
		v_publisher_id VARCHAR2(20) := 'BORIS_PUBLISHER';
		v_source_schema VARCHAR2(20) := 'SCOTT';
		v_subscriber_id VARCHAR2(20) := 'BORIS_SUBSCRIBER';
		v_source_table VARCHAR2(100);
		v_cdc_table VARCHAR2(100);
		v_cdc_view_name VARCHAR2(40);
		our_view_name VARCHAR2(200);
		vSQL VARCHAR2(1000);
		v_err VARCHAR2(10):='OK';
		CURSOR c_tables IS
			SELECT 'emp' table_name FROM dual
				UNION
			SELECT 'dept' table_name FROM dual
		;
BEGIN

-- find a subscription
   	BEGIN
	     vSubhandle :=0;
	     -- get the handle
	     SELECT handle INTO vSubhandle FROM all_subscriptions 
		     WHERE description = v_subscription_description;
	  EXCEPTION
		WHEN OTHERS THEN
		   vSubhandle := 0;
		   dbms_output.put_line('Subscription '||v_subscription_description 
			|| ' does not exist in the database'
		   );
	END;

	FOR tables IN c_tables LOOP
	   v_source_table := UPPER(TRIM(tables.table_name));
	   BEGIN
		v_cdc_table := 'CDC_'||v_source_table;
		v_cdc_view_name := SUBSTR(v_cdc_table,1,LENGTH(v_cdc_table) - 2) || '_vw';
		-- drop the synonym
		BEGIN
		   vSQL := 'DROP SYNONYM ' || v_cdc_view_name;
		   EXECUTE IMMEDIATE vSQL;
		  EXCEPTION WHEN OTHERS THEN NULL;
		END;
		-- drop the subscriber view
	        DBMS_CDC_SUBSCRIBE.DROP_SUBSCRIBER_VIEW(
			SUBSCRIPTION_HANDLE=> vSubhandle,SOURCE_SCHEMA => v_source_schema, SOURCE_TABLE => v_source_table);
	    	DBMS_output.put_line('Subscriber View for CDC table ''' || v_cdc_table || 
			''' was successfully dropped.  Handle # '||TO_CHAR(vSubhandle));
	      EXCEPTION
		WHEN OTHERS THEN
		DBMS_output.put_line('Error start ********************************************');
		DBMS_output.put_line('Error during dropping the subscriber view for ''' 
			|| v_cdc_table || '''  '
			|| ' to the CDC subscription ''' || v_subscription_description || ''':');
		DBMS_output.put_line(to_char(sqlcode)|| ' ' || sqlerrm || '  Handle = '||TO_CHAR(vSubhandle));
		DBMS_output.put_line('Error end   ********************************************');
	   END;
	END LOOP;
END;
END;
/
-- Drop subscription
BEGIN
	DECLARE vSubhandle NUMBER;
		v_subscription_description VARCHAR2(40):='scott -> Datawarehouse';
		v_publisher_id VARCHAR2(20) := 'BORIS_PUBLISHER';
		v_source_schema VARCHAR2(20) := 'SCOTT';
		v_subscriber_id VARCHAR2(20) := 'BORIS_SUBSCRIBER';
		v_source_table VARCHAR2(100);
		v_source_table VARCHAR2(100);
		v_cdc_table VARCHAR2(100);
		v_cdc_view_name VARCHAR2(40);
		our_view_name VARCHAR2(200);
		vSQL VARCHAR2(1000);

BEGIN
   BEGIN
	-- get the handle
	SELECT handle INTO vSubhandle FROM all_subscriptions 
		     WHERE description = v_subscription_description;
	DBMS_LOGMNR_CDC_SUBSCRIBE.DROP_SUBSCRIPTION (SUBSCRIPTION_HANDLE=> vSubhandle);
	DBMS_output.put_line('Subscription ''' || v_subscription_description 
		|| ''' was successfully dropped.  Handle = '||TO_CHAR(vSubhandle));
     EXCEPTION
	WHEN OTHERS THEN
		DBMS_output.put_line('Error start ********************************');
		DBMS_output.put_line('Error during the DROP SUBSCRIPTION step');
		DBMS_output.put_line(to_char(sqlcode)|| ' ' || sqlerrm 
			|| '  Handle = '||TO_CHAR(vSubhandle));
		DBMS_output.put_line('Error end   ********************************');
    END;
END;
END;
/
--revoke table permissions
connect boris_publisher/boris_publisher@whatever

/* Login as a publisher to run this */
BEGIN
	DECLARE work_sql VARCHAR2(32767);
		v_publisher_id VARCHAR2(20) := 'BORIS_PUBLISHER';
		v_source_schema VARCHAR2(20) := 'SCOTT';
		v_subscriber_id VARCHAR2(20) := 'BORIS_SUBSCRIBER';
		v_source_table VARCHAR2(100);
		v_cdc_table VARCHAR2(100);
		v_force_flag VARCHAR2(1);
		CURSOR c_tables IS
			SELECT 'emp' table_name FROM dual
				UNION
			SELECT 'dept' table_name FROM dual
		;
	BEGIN
		FOR tables IN c_tables LOOP
		   v_source_table := UPPER(TRIM(tables.table_name));
		 BEGIN
		   v_cdc_table := 'CDC_'||v_source_table; v_force_flag := 'N';
		   work_sql := 'REVOKE SELECT ON '||v_cdc_table||' FROM '
			|| v_subscriber_id;
		   EXECUTE IMMEDIATE work_sql;
		   DBMS_output.put_line('SELECT permission to ' 
			|| v_cdc_table || ' has been REVOKEed');
		     EXCEPTION
			WHEN OTHERS THEN
			DBMS_output.put_line('Error start **************************');
			DBMS_output.put_line('Error during revoking SELECT permission to ' 
				|| v_cdc_table || ' table:');
			DBMS_output.put_line(to_char(sqlcode)|| ' ' || sqlerrm);
			DBMS_output.put_line('Error end   *************************');
		 END;

		END LOOP;
        END;
END;
/

/* login as a publisher when you run this */
-- drop change tables
BEGIN
	DECLARE work_sql VARCHAR2(32767);
		v_publisher_id VARCHAR2(20) := 'BORIS_PUBLISHER';
		v_source_schema VARCHAR2(20) := 'SCOTT';
		v_source_table VARCHAR2(100);
		v_cdc_table VARCHAR2(100);
		v_force_flag VARCHAR2(1);
		CURSOR c_tables IS
			SELECT 'emp' table_name FROM dual
				UNION
			SELECT 'dept' table_name FROM dual
		;
	BEGIN
-- drop change tables
		FOR tables IN c_tables LOOP
		   v_source_table := UPPER(TRIM(tables.table_name));
		   BEGIN
			v_cdc_table := 'CDC_'||v_source_table; v_force_flag := 'N';
			DBMS_LOGMNR_CDC_PUBLISH.DROP_CHANGE_TABLE (
				OWNER => v_publisher_id, CHANGE_TABLE_NAME => v_cdc_table
				, FORCE_FLAG=> v_force_flag); 
			DBMS_output.put_line('Change table ' || v_cdc_table 
				|| ' was successfully dropped');
		      EXCEPTION
			WHEN OTHERS THEN
			DBMS_output.put_line('Error start *****************************');
			DBMS_output.put_line('Error during attempt to drop change table ' 
				|| v_cdc_table);
			DBMS_output.put_line(to_char(sqlcode)|| ' ' || sqlerrm);
			DBMS_output.put_line('Error end   ****************************');
	 	   END;
		END LOOP;
	END;
END;
/
--revoke permissions for source tables:
connect scott/tiger@whatever
/* Login as an owner to run this */
BEGIN
	DECLARE work_sql VARCHAR2(32767);
		v_publisher_id VARCHAR2(20) := 'BORIS_PUBLISHER';
		v_source_schema VARCHAR2(20) := 'SCOTT';
		v_source_table VARCHAR2(100);
		v_cdc_table VARCHAR2(100);
		v_force_flag VARCHAR2(1);
		CURSOR c_tables IS
			SELECT 'emp' table_name FROM dual
				UNION
			SELECT 'dept' table_name FROM dual
		;
	BEGIN
		FOR tables IN c_tables LOOP
		   v_source_table := UPPER(TRIM(tables.table_name));
		   BEGIN
			   v_cdc_table := 'CDC_'||v_source_table; v_force_flag := 'N';
			   work_sql := 'REVOKE SELECT ON '||v_source_table||' FROM '
				|| v_subscriber_id;
			   EXECUTE IMMEDIATE work_sql;
			   DBMS_output.put_line('SELECT permission to ' || v_source_table || ' has been REVOKEed');
	           EXCEPTION
			WHEN OTHERS THEN
			DBMS_output.put_line('Error start **************************');
			DBMS_output.put_line('Error during REVOKing SELECT permission to ' 
				|| v_source_table || ' table:');
			DBMS_output.put_line(to_char(sqlcode)|| ' ' || sqlerrm);
			DBMS_output.put_line('Error end   **************************');
		   END;
		END LOOP;
        END;
END;
/