7 Replies Latest reply: Apr 25, 2013 12:24 AM by Rc-oracle RSS

    Need Help to Transfer Specific Data with dml_condition

    773556
      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.