/*************************************************************************************
 * @author                        :  Chandar

 * @version                       :  1.0
 *
 * Name of the Application        :  SetParams.sql

 * Creation/Modification History  :
 *
 *    Chandar        12-Feb-2003       Created
 *

 * Overview of Script:
 * This SQL scripts sets maximum SCN parameter for capture and apply process in streams
 * environment.
 * The script is to be executed when AddTable.sql script hangs and waits for a message.
 * This script sets parameters for streams processes and send message to session

 * executing AddTable.sql to continue further.

 * Note:
 * 1. Execute this script only after running AddTable.sql script hangs and waits.

***************************************************************************************/

SPOOL setparams.log

set ECHO OFF
SET VERIFY OFF

PROMPT
-- TNSNAME for database 1

ACCEPT db1 PROMPT 'Enter TNS Name of first database :'

PROMPT
-- Streams administrator username for database 1
ACCEPT strm_adm_db1 PROMPT 'Enter username for streams admin of first database :'

PROMPT
-- Streams administrator password for database 1

ACCEPT strm_adm_pwd_db1 PROMPT 'Enter password for streams admin on first database :'


PROMPT
-- TNSNAME for database 2
ACCEPT db2 PROMPT 'Enter TNS Name of second database :'

PROMPT
-- Streams administrator username for database 2

ACCEPT strm_adm_db2 PROMPT 'Enter username for streams admin of second database :'

PROMPT
-- Streams administrator password for database 2
ACCEPT strm_adm_pwd_db2 PROMPT 'Enter password for streams admin on second database :'



variable scn number;


-- connect as streams administrator to database 1

PROMPT Connecting as streams administrator to database 1

CONN &strm_adm_db1/&strm_adm_pwd_db1@&db1

PROMPT

-- get current SCN at database 1

PROMPT  Getting current SCN of database 1


EXECUTE :scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();

-- set maximum_scn parameter for new capture process to current SCN

PROMPT  Setting maximum SCN for temporary capture process  as current SCN of database 1

exec DBMS_CAPTURE_ADM.SET_PARAMETER( capture_name =>'tempcapture',
                                     parameter =>'maximum_scn',
                                     value =>:scn);

-- connect to database 2 as streams administrator


PROMPT  Connecting as streams administrator to database 2

CONN &strm_adm_db2/&strm_adm_pwd_db2@&db2

-- set maximum_scn parameter for original apply process at database 2
-- to current SCN of database 1

PROMPT
PROMPT  Setting maximum SCN for original apply process at database 2
        to current SCN of database 1

EXECUTE DBMS_APPLY_ADM.SET_PARAMETER ( apply_name => 'apply_db2',

                                       parameter =>'maximum_scn',
                                       value =>:scn);

-- connect as streams administrator to database 1

PROMPT Connecting as streams administrator to database 1

CONN &strm_adm_db1/&strm_adm_pwd_db1@&db1

PROMPT Sending message to previous session to continue

EXECUTE wait_until('s2','s1','1.2');

PROMPT End of script


SPOOL OFF