(SET VERIFY ON
SET HEAD ON
SET FEEDBACK 1
SET ARRAY 1
SET LINESIZE 10000
SET PAGESIZE 50000
SET LONG 10000
SET ECHO ON
SET TRIMSPOOL ON
SET COLSEP ';'
SET SERVEROUT OFF
clear screen
SPOOL ON
SET SQLBLANKLINES ON
SET SERVEROUTPUT ON
SET ERRORLOGGING ON
SET ECHO ON
prompt Welcome to Application Template PDB Configuration
SPOOL "&SPOOL_PATH"
/* CDB sys user name and password to be given */
accept P_CDB_USER Prompt 'Enter CDB Schema Username: [Eg: sys]'
accept P_CDB_PWD Prompt 'Enter CDB Schema Password: [Eg: PASSWORD]'
accept P_CDB_HOST Prompt 'Enter CDB Schema Host: [Eg: fcubs.in.oracle.com]'
accept P_CDB_PORT Prompt 'Enter CDB Schema Port: [Eg: 1521]'
accept P_CDB_NAME Prompt 'Enter CDB Service Name: [Eg: FCUBSCDB]'
accept P_DB_MOUNTED_PATH Prompt 'Enter CDB mounted path: [Eg: /scratch/db1800dat]'

accept P_APP_TEMPLATE_PDB Prompt 'Enter Name for Application Template PDB to be created: [Eg: pdbfcubs]'
accept P_COMMON_USER Prompt 'Enter Common Username to be created: [Eg: fcubs]'
accept P_COMMON_USER_PWD Prompt 'Enter Pwd for Common User : [Eg: fcubs]'
accept P_COMMON_TSPACE Prompt 'Enter TableSpace Name :[Eg: fcubs]'

/* Connecting to CDB as sysdba */
CONN &P_CDB_USER/&P_CDB_PWD@&P_CDB_NAME AS sysdba;

create pluggable database &P_APP_TEMPLATE_PDB ADMIN USER sourceadmin IDENTIFIED BY sourceadmin file_name_convert=('pdbseed','&P_APP_TEMPLATE_PDB');

alter pluggable database &P_APP_TEMPLATE_PDB open;
alter pluggable database &P_APP_TEMPLATE_PDB save state;

/*connecting to template pdb as sysdba */
conn &P_CDB_USER/&P_CDB_PWD@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=&P_CDB_HOST)(PORT=&P_CDB_PORT)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=&P_APP_TEMPLATE_PDB))) as sysdba;
create tablespace &P_COMMON_USER datafile '&P_DB_MOUNTED_PATH/&P_CDB_NAME/&P_APP_TEMPLATE_PDB/&P_COMMON_TSPACE..dbf' size 100M autoextend on next 10M maxsize 30000M;

CREATE USER &P_COMMON_USER IDENTIFIED BY &P_COMMON_USER_PWD default tablespace &P_COMMON_USER quota unlimited on &P_COMMON_USER;

grant execute on dbms_sql to &P_COMMON_USER;
grant execute on dbms_lock to &P_COMMON_USER;
grant execute on dbms_job to &P_COMMON_USER;
grant execute on dbms_alert to &P_COMMON_USER;
grant execute on dbms_refresh to &P_COMMON_USER;
grant execute on dbms_pipe to &P_COMMON_USER;
grant execute on dbms_shared_pool to &P_COMMON_USER;
grant execute on dbms_application_info to &P_COMMON_USER;
grant execute on utl_file to &P_COMMON_USER;
grant select on v_$process to &P_COMMON_USER;
grant select on v_$session to &P_COMMON_USER;
grant select on v_$instance to &P_COMMON_USER;
grant select on v_$timer to &P_COMMON_USER;
grant select on v_$database to &P_COMMON_USER;
grant select on v_$parameter to &P_COMMON_USER;
grant select on v_$nls_parameters to &P_COMMON_USER;
grant select on dba_jobs_running to &P_COMMON_USER;
grant create session to &P_COMMON_USER;
grant create synonym to &P_COMMON_USER;
grant create view to &P_COMMON_USER;
grant create sequence to &P_COMMON_USER;
grant create table to &P_COMMON_USER;
grant create procedure to &P_COMMON_USER;
grant create trigger to &P_COMMON_USER;
grant create type to &P_COMMON_USER;
grant create library to  &P_COMMON_USER;
grant create database link to  &P_COMMON_USER;
grant create any synonym to  &P_COMMON_USER;
grant select on dba_jobs to &P_COMMON_USER;
grant create database link to &P_COMMON_USER;
grant create materialized view to &P_COMMON_USER;
grant execute on dbms_aq to &P_COMMON_USER;
grant execute on dbms_aqadm to &P_COMMON_USER;
grant execute on dbms_job to  &P_COMMON_USER;
grant execute on dbms_lock to  &P_COMMON_USER;
grant execute on dbms_pipe to  &P_COMMON_USER;
grant execute on dbms_refresh to  &P_COMMON_USER;
grant execute on dbms_rls to  &P_COMMON_USER;
create public synonym dbms_shared_pool for sys.dbms_shared_pool;
grant execute on dbms_shared_pool to  &P_COMMON_USER;
grant execute on dbms_sql to  &P_COMMON_USER;
grant execute on utl_file to  &P_COMMON_USER;
grant select on SYS.TRANSPORT_SET_VIOLATIONS to  &P_COMMON_USER;
grant create evaluation context to  &P_COMMON_USER;
grant create rule to  &P_COMMON_USER;
grant create job to  &P_COMMON_USER;
grant create rule set to  &P_COMMON_USER;
grant exp_full_database to  &P_COMMON_USER; 
grant alter tablespace to  &P_COMMON_USER;
grant manage tablespace to  &P_COMMON_USER;
grant execute on DBMS_FILE_TRANSFER to  &P_COMMON_USER;
grant execute on SYS.DBMS_TTS to  &P_COMMON_USER; 
grant execute on SYS.DBMS_DATAPUMP to  &P_COMMON_USER;
grant JAVAUSERPRIV to &P_COMMON_USER;
grant execute on dbms_scheduler to  &P_COMMON_USER;
create public synonym UTL_RECOMP for sys.UTL_RECOMP;
grant execute on UTL_RECOMP to &P_COMMON_USER;
grant execute on DBMS_MONITOR to &P_COMMON_USER;
grant select on dba_directories to &P_COMMON_USER;
grant execute on DBMS_CRYPTO to &P_COMMON_USER;
grant select on gv_$session to &P_COMMON_USER;
grant create any directory to &P_COMMON_USER;
grant select on SYS.DBA_SCHEDULER_RUNNING_JOBS to &P_COMMON_USER;
grant execute on sys.dbms_redact to &P_COMMON_USER; 
grant SELECT on sys.redaction_policies to &P_COMMON_USER; 
grant SELECT on sys.redaction_columns to &P_COMMON_USER; 
grant SELECT on sys.redaction_values_for_type_full to &P_COMMON_USER; 
grant create session,connect,resource to &P_COMMON_USER;
grant SELECT ON dba_applications to &P_COMMON_USER; 
grant SELECT ON dba_app_versions to &P_COMMON_USER;
grant dba to &P_COMMON_USER;

SET ECHO OFF
clear screen
spool off