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

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

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

 * Overview of Script:
 *
 * This script creates a streams administrator user and grants it necessary
 * privileges to manage the streams in database. It also creates a separate
 * tablespace for streams administrator and logminer.

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

SET VERIFY OFF
SPOOL streams_config.log



-- get TNSNAME of the database where streams is to be configured

ACCEPT dbname PROMPT 'Enter TNS Name of the database :'

-- get the password for SYS user of database

ACCEPT syspwd PROMPT 'Enter password for sys user of the database :'


conn sys/&syspwd@&dbname as sysdba

-- get a username for stream administrator

ACCEPT strmadm PROMPT 'Enter a user name for streams admin user :'

-- get a password for streams administrator


ACCEPT strmadmpwd PROMPT 'Enter password for streams admin user :'

--create the user and grant it connect and resource roles

PROMPT Creating streams admin user and granting CONNECT and RESOURCE roles to it

GRANT CONNECT, RESOURCE TO &strmadm IDENTIFIED BY &strmadmpwd;


-- grant minimum privileges required to manage streams

PROMPT Granting various privileges to streams administrator

PROMPT Granting EXECUTE ON DBMS_AQADM package

GRANT EXECUTE ON DBMS_AQADM TO &strmadm;

PROMPT Granting EXECUTE ON DBMS_STREAMS_ADM package


GRANT EXECUTE ON DBMS_STREAMS_ADM TO &strmadm;

PROMPT Granting CREATE_RULE_SET_OBJ system privilege

BEGIN
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
    grantee      => '&strmadm',
    grant_option => FALSE);
END;
/


PROMPT Granting CREATE_RULE_OBJ system privilege

BEGIN
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => DBMS_RULE_ADM.CREATE_RULE_OBJ,
    grantee      => '&strmadm',
    grant_option => FALSE);
END;
/

-- grant privilege to manage streams apply processes in database


PROMPT Granting EXECUTE ON DBMS_APPLY_ADM package

GRANT EXECUTE ON DBMS_APPLY_ADM TO &strmadm;

-- grant privilege to manage streams capture processes in database

PROMPT Granting EXECUTE ON DBMS_CAPTURE_ADM  package

GRANT EXECUTE ON DBMS_CAPTURE_ADM TO &strmadm;

-- grant privilege to manage streams propagation processes in database


PROMPT Granting EXECUTE ON DBMS_PROPAGATION_ADM  package

GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO &strmadm;

--grant privilege to obtain the current SCN for a database

PROMPT Granting EXECUTE ON DBMS_FLASHBACK package

GRANT EXECUTE ON DBMS_FLASHBACK TO &strmadm;

-- grant privilege to select data dictionary tables and views

PROMPT Granting SELECT_CATALOG_ROLE


GRANT SELECT_CATALOG_ROLE TO &strmadm;


-- grant privilege to select from DBA_APPLY_ERROR view within a PL/SQL subprogram

PROMPT Granting EXECUTE ON DBA_APPLY_ERROR package

GRANT SELECT ON DBA_APPLY_ERROR TO &strmadm;


-- get the directory path where datafile for streams tablespace should be created.

ACCEPT dir_path PROMPT 'Enter directory path where new tablespace file for
streams should be created -E.g. /usr/oracle/dbs:'



-- create a tablespace to be used by streams administrator

PROMPT Creating new tablespace for streams administrator

CREATE TABLESPACE streams_tbs DATAFILE '&dir_path/streams_tbs.dbf'
  SIZE 25 M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;


-- set the default tablespace for streams administrator as streams_tbs

PROMPT Assigning new tablespace to streams administrator user

ALTER USER &strmadm DEFAULT TABLESPACE streams_tbs  QUOTA UNLIMITED ON streams_tbs;



-- get the directory path where datafile for logminer
--tablespace should be created.

ACCEPT dir_path PROMPT 'Enter directory path where tablespace file for
logminer should be created -E.g. /usr/oracle/dbs):'


-- create a tablespace to be used by logmner

PROMPT Creating new tablespace for logminer

CREATE TABLESPACE logmnrts DATAFILE '&dir_path/logmnrts.dbf'
  SIZE 25 M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;


-- set the tablespace for logminer as above tablespace


PROMPT Assigning new tablespace to logminer

EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnrts');


PROMPT End of Script

SPOOL OFF