--

-- Set up queue "STRMREP.APP_QUE_ERATING"

--

SQL> BEGIN

   dbms_streams_adm.set_up_queue(

     queue_table => 'STRMREP.TAB_APP_QUE_ERATING',

     storage_clause => NULL,

     queue_name => 'STRMREP.APP_QUE_ERATING',

     queue_user => '');

END;

/

--

-- APPLY changes for schema ENCLE

--

SQL> BEGIN

   dbms_streams_adm.add_schema_rules(

     schema_name => 'ENCLE',

     streams_type => 'APPLY',

     streams_name => 'APP_ERATING',

     queue_name => 'STRMREP.APP_QUE_ERATING',

     include_dml => TRUE,

     include_ddl => TRUE,

     include_tagged_lcr => TRUE,

     source_database => 'ESUITE.NET',

     inclusion_rule => TRUE,

     and_condition => ':lcr.get_compatible() <= dbms_streams.compatible_10_2');

END;

/

--

-- APPLY changes for schema SCOTT, SCOTT用户要自动同步到SCOTTNEW用户下

-- 需要用到DBMS_STREAMS_ADM.RENAME_SCHEMA 过程.

SQL> DECLARE

vi_dml_rule VARCHAR2(40);

vi_ddl_rule VARCHAR2(40);

BEGIN

   dbms_streams_adm.add_schema_rules(

     schema_name => 'SCOTT',

     streams_type => 'APPLY',

     streams_name => 'APP_ERATING',

     queue_name => 'STRMREP.APP_QUE_ERATING',

     include_dml => TRUE,

     include_ddl => TRUE,

     include_tagged_lcr => TRUE,

     source_database => 'ESUITE.NET',

     dml_rule_name => vi_dml_rule,

ddl_rule_name => vi_ddl_rule,

     inclusion_rule => TRUE,

     and_condition => ':lcr.get_compatible() <= dbms_streams.compatible_10_2');

    

    /* wangnc added */

    DBMS_STREAMS_ADM.RENAME_SCHEMA(

rule_name => vi_dml_rule,

from_schema_name => 'SCOTT',

to_schema_name => 'SCOTTNEW'

);

END;

/

--

-- Get tag value to be used for Apply

--

SQL> DECLARE

   found    BINARY_INTEGER := 0;

   tag_num NUMBER;

BEGIN

   -- Use the apply object id as the tag

   SELECT o.object_id INTO tag_num

   FROM dba_objects o

   WHERE o.object_name= 'APP_ERATING' AND

         o.object_type='APPLY';

   LOOP

     BEGIN

       found := 0;

       SELECT 1 INTO found FROM dba_apply

       WHERE apply_name != 'APP_ERATING' AND

             apply_tag = hextoraw(tag_num);

     EXCEPTION WHEN no_data_found THEN

       EXIT;

     END;

     EXIT WHEN (found = 0);

     tag_num := tag_num + 1;

   END LOOP;

   -- alter apply

   dbms_apply_adm.alter_apply(

     apply_name => 'APP_ERATING',

     apply_tag => hextoraw(tag_num));

END;

/

--

-- Start apply process APP_ERATING

--

SQL> BEGIN

   dbms_apply_adm.set_parameter(apply_name => 'APP_ERATING', parameter => 'DISABLE_ON_ERROR', value => 'N');

   dbms_apply_adm.start_apply(

     apply_name => 'APP_ERATING');

EXCEPTION WHEN OTHERS THEN

   IF sqlcode = -26666 THEN NULL;   -- APPLY process already running

   ELSE RAISE;

   END IF;

END;

/

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

-- connect as streams administrator to source site

SQL> CONNECT strmrep/xxx@db_esuite

--

-- Enable propagation schedule for "STRMREP.CAP_QUE_ERATING" to YITIAN.LK

--

SQL> BEGIN

   dbms_aqadm.enable_propagation_schedule(

     queue_name => 'STRMREP.CAP_QUE_ERATING',

     destination => 'YITIAN.LK',

     destination_queue => 'STRMREP.APP_QUE_ERATING');

EXCEPTION WHEN OTHERS THEN

   IF sqlcode = -24064 THEN NULL; -- propagation already enabled

   ELSE RAISE;

   END IF;

END;

/

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

至此全部结束.

--End--