REM
REM    The following sections set up streams at the destination database
REM
ACCEPT dest_dba_passwd PROMPT 'Please enter the password for user SYS at the destination database DESDB : ' HIDE
ACCEPT dest_strmadmin_passwd PROMPT 'Please enter the password for user STRMADMIN at the destination database DESDB : ' HIDE
connect SYS/&dest_dba_passwd@DESDB as SYSDBA


REM
REM    Create Streams Administrator
REM
create user "STRMADMIN" identified by "strmadmin";


REM
REM    The following section grants the privileges to Streams administrator
REM
grant CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE to "STRMADMIN";

GRANT DBA TO STRMADMIN;

GRANT EXECUTE ON sys.dbms_aq TO STRMADMIN;

GRANT EXECUTE ON sys.dbms_aqadm TO STRMADMIN;

BEGIN 
 DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
   privilege     => 'ENQUEUE_ANY', 
   grantee       => 'STRMADMIN', 
   admin_option  => FALSE);
END;
/


BEGIN 
 DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
   privilege     => 'DEQUEUE_ANY', 
   grantee       => 'STRMADMIN', 
   admin_option  => FALSE);
END;
/


BEGIN 
 DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
   privilege     => 'MANAGE_ANY', 
   grantee       => 'STRMADMIN', 
   admin_option  => TRUE);
END;
/


BEGIN
 DBMS_AQADM.GRANT_TYPE_ACCESS(
   user_name     => 'STRMADMIN');
END;
/


BEGIN
 DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege     => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ, 
    grantee       => 'STRMADMIN', 
    grant_option  => TRUE);
 DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege     => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, 
    grantee       => 'STRMADMIN', 
    grant_option  => TRUE);
 DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege     => DBMS_RULE_ADM.CREATE_RULE_OBJ, 
    grantee       => 'STRMADMIN', 
    grant_option  => TRUE);
END;
/


BEGIN
 DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege     => DBMS_RULE_ADM.CREATE_ANY_RULE_SET,  
    grantee       => 'STRMADMIN', 
    grant_option  => TRUE);
 DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege     => DBMS_RULE_ADM.ALTER_ANY_RULE_SET,  
    grantee       => 'STRMADMIN', 
    grant_option  => TRUE);
 DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege     => DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET,  
    grantee       => 'STRMADMIN', 
    grant_option  => TRUE);
 DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege     => DBMS_RULE_ADM.CREATE_ANY_RULE,  
    grantee       => 'STRMADMIN', 
    grant_option  => TRUE);
 DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege     => DBMS_RULE_ADM.ALTER_ANY_RULE,  
    grantee       => 'STRMADMIN', 
    grant_option  => TRUE);
 DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege     => DBMS_RULE_ADM.EXECUTE_ANY_RULE,  
    grantee       => 'STRMADMIN', 
    grant_option  => TRUE);
END;
/


BEGIN
 DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
    privilege     => DBMS_RULE_ADM.EXECUTE_ON_EVALUATION_CONTEXT,  
    object_name   => 'SYS.STREAMS$_EVALUATION_CONTEXT',
    grantee       => 'STRMADMIN', 
    grant_option  => FALSE );
END;
/


GRANT EXECUTE ON sys.dbms_capture_adm TO STRMADMIN;

GRANT EXECUTE ON sys.dbms_apply_adm TO STRMADMIN;

GRANT EXECUTE ON sys.dbms_rule_adm TO STRMADMIN;

GRANT SELECT_CATALOG_ROLE TO STRMADMIN;

BEGIN
 DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege     => DBMS_RULE_ADM.EXECUTE_ANY_EVALUATION_CONTEXT,  
    grantee       => 'STRMADMIN', 
    grant_option  => TRUE);
END;
/


GRANT EXECUTE ON SYS.dbms_streams_adm TO STRMADMIN;

GRANT ALL PRIVILEGES TO STRMADMIN;

connect STRMADMIN/&dest_strmadmin_passwd@DESDB


REM
REM    Create streams queue
REM
BEGIN
 DBMS_STREAMS_ADM.SET_UP_QUEUE(
   queue_user => 'STRMADMIN');
END;
/



REM
REM    Add apply rules for tables at the destination database
REM
BEGIN
 DBMS_STREAMS_ADM.ADD_TABLE_RULES(
   table_name => '"des"."AZAD"', 
   streams_type => 'APPLY', 
   streams_name => 'STRMADMIN_SRCDB_REGRESS_R', 
   queue_name => '"STRMADMIN"."STREAMS_QUEUE"', 
   include_dml => true, 
   include_ddl => false, 
   source_database => 'SRCDB.REGRESS.RDBMS.DEV.US.ORACLE.COM');
END;
/





REM
REM    The following sections set up streams at the source database
REM
ACCEPT source_dba_passwd PROMPT 'Please enter the password for user SYS at the source database SRCDB : ' HIDE
ACCEPT source_strmadmin_passwd PROMPT 'Please enter the password for user STRMADMIN at the source database SRCDB : ' HIDE
connect SYS/&source_dba_passwd@SRCDB as SYSDBA


REM
REM    Turn on supplemental logging
REM
ALTER table src.azad ADD SUPPLEMENTAL LOG DATA (all) COLUMNS;


REM
REM    Switch log file
REM
ALTER SYSTEM SWITCH LOGFILE;


REM
REM    Create Streams Administrator
REM
create user "STRMADMIN" identified by "strmadmin";


REM
REM    The following section grants the privileges to Streams administrator
REM
grant CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE to "STRMADMIN";

GRANT DBA TO STRMADMIN;

GRANT EXECUTE ON sys.dbms_aq TO STRMADMIN;

GRANT EXECUTE ON sys.dbms_aqadm TO STRMADMIN;

BEGIN 
 DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
   privilege     => 'ENQUEUE_ANY', 
   grantee       => 'STRMADMIN', 
   admin_option  => FALSE);
END;
/


BEGIN 
 DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
   privilege     => 'DEQUEUE_ANY', 
   grantee       => 'STRMADMIN', 
   admin_option  => FALSE);
END;
/


BEGIN 
 DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
   privilege     => 'MANAGE_ANY', 
   grantee       => 'STRMADMIN', 
   admin_option  => TRUE);
END;
/


BEGIN
 DBMS_AQADM.GRANT_TYPE_ACCESS(
   user_name     => 'STRMADMIN');
END;
/


BEGIN
 DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege     => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ, 
    grantee       => 'STRMADMIN', 
    grant_option  => TRUE);
 DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege     => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, 
    grantee       => 'STRMADMIN', 
    grant_option  => TRUE);
 DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege     => DBMS_RULE_ADM.CREATE_RULE_OBJ, 
    grantee       => 'STRMADMIN', 
    grant_option  => TRUE);
END;
/


BEGIN
 DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege     => DBMS_RULE_ADM.CREATE_ANY_RULE_SET,  
    grantee       => 'STRMADMIN', 
    grant_option  => TRUE);
 DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege     => DBMS_RULE_ADM.ALTER_ANY_RULE_SET,  
    grantee       => 'STRMADMIN', 
    grant_option  => TRUE);
 DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege     => DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET,  
    grantee       => 'STRMADMIN', 
    grant_option  => TRUE);
 DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege     => DBMS_RULE_ADM.CREATE_ANY_RULE,  
    grantee       => 'STRMADMIN', 
    grant_option  => TRUE);
 DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege     => DBMS_RULE_ADM.ALTER_ANY_RULE,  
    grantee       => 'STRMADMIN', 
    grant_option  => TRUE);
 DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege     => DBMS_RULE_ADM.EXECUTE_ANY_RULE,  
    grantee       => 'STRMADMIN', 
    grant_option  => TRUE);
END;
/


BEGIN
 DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
    privilege     => DBMS_RULE_ADM.EXECUTE_ON_EVALUATION_CONTEXT,  
    object_name   => 'SYS.STREAMS$_EVALUATION_CONTEXT',
    grantee       => 'STRMADMIN', 
    grant_option  => FALSE );
END;
/


GRANT EXECUTE ON sys.dbms_capture_adm TO STRMADMIN;

GRANT EXECUTE ON sys.dbms_apply_adm TO STRMADMIN;

GRANT EXECUTE ON sys.dbms_rule_adm TO STRMADMIN;

GRANT SELECT_CATALOG_ROLE TO STRMADMIN;

BEGIN
 DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege     => DBMS_RULE_ADM.EXECUTE_ANY_EVALUATION_CONTEXT,  
    grantee       => 'STRMADMIN', 
    grant_option  => TRUE);
END;
/


GRANT EXECUTE ON SYS.dbms_streams_adm TO STRMADMIN;

GRANT ALL PRIVILEGES TO STRMADMIN;

connect STRMADMIN/&source_strmadmin_passwd@SRCDB


REM
REM    Create a database link
REM
CREATE DATABASE LINK "DESDB.REGRESS.RDBMS.DEV.US.ORACLE.COM" connect to "STRMADMIN" identified by "strmadmin" using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=kholghi)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=desdb)))';


REM
REM    Create streams queue
REM
BEGIN
 DBMS_STREAMS_ADM.SET_UP_QUEUE(
   queue_user => 'STRMADMIN');
END;
/



REM
REM    Add capture rules for tables at the source database
REM
BEGIN
 DBMS_STREAMS_ADM.ADD_TABLE_RULES(
   table_name => '"SRC"."AZAD"', 
   streams_type => 'CAPTURE', 
   streams_name => 'STRMADMIN_CAPTURE', 
   queue_name => '"STRMADMIN"."STREAMS_QUEUE"', 
   include_dml => true, 
   include_ddl => false, 
   source_database => 'SRCDB.REGRESS.RDBMS.DEV.US.ORACLE.COM');
END;
/



REM
REM    Add propagation rules for tables at the source database
REM
BEGIN
 DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
   table_name => '"SRC"."AZAD"', 
   streams_name => 'STRMADMIN_PROPAGATE', 
   source_queue_name => '"STRMADMIN"."STREAMS_QUEUE"', 
   destination_queue_name => '"STRMADMIN"."STREAMS_QUEUE"@DESDB.REGRESS.RDBMS.DEV.US.ORACLE.COM', 
   include_dml => true, 
   include_ddl => false, 
   source_database => 'SRCDB.REGRESS.RDBMS.DEV.US.ORACLE.COM');
END;
/