查询Streams中SYNC和APPEND的表各有哪些

sql

--connect to database,P as sample
CONNECT strmadmin/[email protected]
--select all table name,database,dml_handlers mode which using streams,and create a temp table T_TMP
CREATE TABLE T_TMP AS (SELECT distinct OBJECT_OWNER,OBJECT_NAME,USER_PROCEDURE
FROM DBA_APPLY_DML_HANDLERS
WHERE USER_PROCEDURE NOT LIKE '%ERRORS%');
--update mode name to SYNC or APPEND
UPDATE T_TMP SET USER_PROCEDURE = 'APPEND' WHERE USER_PROCEDURE LIKE '%CCD%';
UPDATE T_TMP SET USER_PROCEDURE = 'SYNC' WHERE USER_PROCEDURE LIKE '%COPY_ONLY%';
--display the result
SELECT * FROM T_TMP;
--drop the tmp table
DROP TABLE T_TMP;

使用PL/SQL

DECLARE

	   v_db DBA_APPLY_DML_HANDLERS.OBJECT_OWNER%TYPE;
	   v_table DBA_APPLY_DML_HANDLERS.OBJECT_NAME%TYPE;
	   v_mode DBA_APPLY_DML_HANDLERS.USER_PROCEDURE%TYPE;
	   t_mode DBA_APPLY_DML_HANDLERS.USER_PROCEDURE%TYPE;

	   CURSOR han_cur IS 
	   		  SELECT distinct OBJECT_OWNER,OBJECT_NAME,USER_PROCEDURE
	   		  FROM DBA_APPLY_DML_HANDLERS
	   		  WHERE USER_PROCEDURE NOT LIKE '%ERRORS%';


BEGIN
	DBMS_OUTPUT.ENABLE(1000000);
	OPEN han_cur;
	FETCH han_cur INTO v_db,v_table,v_mode; 

	WHILE han_cur%FOUND LOOP
	IF v_mode LIKE '%CCD%' 
	THEN
		t_mode := 'APPEND';
	ELSE IF v_mode LIKE '%COPY%' 
		 THEN
		 	 t_mode := 'SYNC';
		 ELSE 
		 	 t_mode := v_mode;
		 END IF;
	END IF;
	DBMS_OUTPUT.PUT_LINE(v_db||','||v_table||','||t_mode);
	FETCH han_cur INTO v_db,v_table,v_mode;
	END LOOP; 
	CLOSE han_cur;
END;
/