This is a library for DDL Operations
This will be the repository of the functions

[code language=”sql”]

#!/bin/ksh
######################################################################

gg_ext_det()
{

# read the environment file for gg here
# . /var/opt/oracle/.ggsora12_env
#
# info extract <extract name>
# send EP1ALSDM, report

echo
echo "Extract detail…………….."
echo

/oracle/product/12.1/gg_1/ggsci <<EOF

sh date

send <extract name>, status

send <extract name>, showtrans

send <extract name> getlag

stats <extract name> totalsonly *.*, reportrate sec

info <extract name>, showch

EOF

}

cr_dba_gg_tst_usr()
{
######################################################################
# function Name: cr_dba_gg_tst_usr
# Description..: Create schema/user for golden gate testing
# Author…….: Michael Culp
# Date………: 05/09/2016
# Version……: 1.0
# Modified By..:
# Date Modified:
# Comments…..:
# Schema owner.: DBA_GG_TST
# alter session set current
# Login User…:
# Run Order….:
# Dependent on.:
######################################################################

sqlplus -s "/ as sysdba" <<EOF

set echo on
set feed on
set time on
set timing on

spool logs/cr_dba_gg_tst_usr.log

— drop role GG_APP_ROLE;
—
— GG_APP_ROLE (Role)
—
— create role CST_L01_APP_ROLE not identified;
— grant create session to cst_L01_APP_ROLE;
— grant create synonym to cst_L01_APP_ROLE;

— drop role cst_L01_VIEW_ROLE;
—
— cst_L01_VIEW_ROLE (Role)
—
— create role CST_L01_VIEW_ROLE not identified;
— grant create session to cst_L01_VIEW_ROLE;

—
— CREATE USER dba_gg_test (Schema Owner GoldenGate Test user)
—

drop user DBA_GG_TST cascade;

create user DBA_GG_TST identified by gg5d01_o
default tablespace mrcxx_data
temporary tablespace temp
profile exempt_app_profile;

grant create session to DBA_GG_TST;
grant dba to DBA_GG_TST;
alter user DBA_GG_TST quota unlimited on mrcxx_data;

— grant application_owner_role to cst_L01_owner;
— revoke unlimited tablespace from cst_L01_owner;
— alter user cst_L01_owner default role all;

—
— CREATE USER CST_L01_USER (User Account)
—

— drop user cst_L01_user cascade;

— create user cst_L01_user identified by c5td01_u
— default tablespace mrcxx_data
— temporary tablespace temp
— profile exempt_app_profile;

— grant CST_L01_APP_ROLE to CST_L01_USER;
— revoke unlimited tablespace from cst_L01_user;
— alter user cst_L01_user quota unlimited on mrcxx_data;
— alter user cst_L01_user default role all;

—
— CREATE USER cst_L01_read (READ ONLY Account)
—

— drop user cst_L01_read cascade;

— create user cst_L01_read identified by c5td01_r
— default tablespace piper_data
— temporary tablespace temp
— profile exempt_app_profile;

— grant cst_L01_VIEW_ROLE to cst_L01_read;
— revoke unlimited tablespace from cst_L01_read;
— alter user cst_L01_read quota unlimited on piper_data;
— alter user cst_L01_read default role all;

commit;
spool off
exit

EOF

}

[/code]