This discussion is archived
7 Replies Latest reply: Apr 24, 2013 10:24 PM by reena - oracle RSS

Need Help to Transfer Specific Data with dml_condition

773556 Newbie
Currently Being Moderated
i have two databases on two different server with name db1 and db2. i want to transfer specific data with dml condition. bellow is my code
*************************************************************************
for Server have db1.
*****************************************************************************
--------------Sys----------------------
create user strmadmin identified by strmadmin;

grant connect, resource, dba to strmadmin;

begin dbms_streams_auth.grant_admin_privilege
(grantee => 'strmadmin',
grant_privileges => true);
end;
grant select_catalog_role, select any dictionary to strmadmin;

alter system set global_names=true;
alter system set streams_pool_size = 100 m;

----------------------end--------------------

-----------------------StrmAdmin--------------------------
create database link db2
connect to strmadmin
identified by strmadmin
using 'DB2';
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

EXEC DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(table_name => scott.emp');

-- Configure capture process at the source database

begin dbms_streams_adm.add_table_rules
( table_name => 'scott.emp',
streams_type => 'capture',
streams_name => 'capture_stream',
queue_name=> 'strmadmin.streams_queue',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
end;
-- -- Configure Sub Set Rules capture process at the source database

begin dbms_streams_adm.add_subset_rules
( table_name => 'scott.emp',
dml_condition=>'deptno=50',
streams_type => 'capture',
streams_name => 'capture_stream',
queue_name=> 'strmadmin.streams_queue',
include_tagged_lcr => true);
end;

--     Configure the propagation process at Sources Database

begin dbms_streams_adm.add_table_propagation_rules
( table_name => 'scott.emp',
streams_name => 'DB1_TO_DB2',
source_queue_name => 'strmadmin.streams_queue',
destination_queue_name => 'strmadmin.streams_queue@DB2',
include_dml => true,
include_ddl => true,
source_database => 'DB1',
inclusion_rule => true);
end;
--     Configure the Subset propagation Rule process at Sources Database

begin SYS.dbms_streams_adm.add_subset_propagation_rules
( table_name => 'scott.emp',
dml_condition=>'deptno=50',
streams_name => 'DB1_TO_DB2',
source_queue_name => 'strmadmin.streams_queue',
destination_queue_name => 'strmadmin.streams_queue@DB2',
include_tagged_lcr => true);
end;

--      Set the instantiation system change number (SCN)
declare
source_scn number;
begin
source_scn := dbms_flashback.get_system_change_number();
dbms_apply_adm.set_table_instantiation_scn@DB2
( source_object_name => 'scott.emp',
source_database_name => 'DB1',
instantiation_scn => source_scn);
end;
--      Start the capture processes
begin dbms_capture_adm.start_capture
( capture_name => 'capture_stream');
end;

---------------------------End----------------------------------------------------------
*****************************************************************************
for server 2 have db2.
*****************************************************************************
--------------------------Sys---------------------------------------------------------------
CREATE USER strmadmin IDENTIFIED BY strmadmin;

GRANT CONNECT, RESOURCE, DBA TO strmadmin;

BEGIN
DBMS_STREAMS_AUTH.grant_admin_privilege (grantee => 'strmadmin',
grant_privileges => TRUE);
END;

GRANT SELECT_CATALOG_ROLE, SELECT ANY DICTIONARY TO strmadmin;

ALTER SYSTEM SET global_names=TRUE;
ALTER SYSTEM SET streams_pool_size = 100 M;

-----------------------------------------------------------End-----------------------------


---------------------------------Stream user--------------------------------------------------------------

CREATE DATABASE LINK db1
CONNECT TO strmadmin
IDENTIFIED BY strmadmin
USING 'DB1';
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

EXEC DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(table_name => scott.emp');

-- add table Level rule on target Database.

BEGIN
DBMS_STREAMS_ADM.add_table_rules (
table_name => 'scott.emp',
streams_type => 'apply',
streams_name => 'apply_stream',
queue_name => 'strmadmin.streams_queue',
include_dml => TRUE,
include_ddl => TRUE,
source_database => 'DB1',
inclusion_rule => TRUE);
END;

-- add table Level Sub Set rule on target Database.

BEGIN
DBMS_STREAMS_ADM.add_subset_rules (
table_name => 'scott.emp',
dml_condition => 'deptno=50',
streams_type => 'apply',
streams_name => 'apply_stream',
queue_name => 'strmadmin.streams_queue',
include_tagged_lcr => TRUE);
END;

-- Start the apply processes

BEGIN
DBMS_APPLY_ADM.set_parameter (apply_name => 'apply_stream',
parameter => 'disable_on_error',
VALUE => 'n');
END;

BEGIN
DBMS_APPLY_ADM.start_apply (apply_name => 'apply_stream');
END;

---------------------------------End---------------------------------------------------------------------------------
plz help me.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points