Prerequisites

Syntax

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 Approot and ApprootSeed Configuration
SPOOL "&SPOOL_PATH"
/* Inputs are recieved */
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 Schema Name: [Eg: FCUBSCDB]'
accept P_DB_MOUNTED_PATH Prompt 'Enter CDB mounted path for approot application seed creation[Eg: /scratch/db1800dat] :'

accept P_TEMPLATE_PDB Prompt 'Enter Template PDB Name: [Eg: pdbfcubs]'
accept P_APPROOT_NAME Prompt 'Enter Approot Name: [Eg: appfcubs]'
accept P_PDB_TO_APPPDB Prompt 'Please provide path for pdb_to_apppdb.sql: [E.g: C:\app\client\user\product\19.0.0\client_1\rdbms\admin\pdb_to_apppdb.sql]'
accept P_COMMON_USER Prompt 'Enter Common Username created in Template PDB: [E.g: fcubs]'

/* Connecting to cdb
conn sys/FC142SYS18C@fc142cbd as sysdba */
CONN &P_CDB_USER/&P_CDB_PWD@&P_CDB_NAME AS sysdba;

/* Creating the Approot */
CREATE pluggable database &P_APPROOT_NAME AS application container FROM &P_TEMPLATE_PDB file_name_convert=('&P_TEMPLATE_PDB','&P_APPROOT_NAME');
ALTER pluggable database &P_APPROOT_NAME open;

/* Connecting to Approot 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_APPROOT_NAME))) as sysdba;

grant select on v_$session to &P_COMMON_USER container=all;
grant create session to &P_COMMON_USER container=all;
grant select on gv_$session to &P_COMMON_USER container=all;
grant select on gv_$session to &P_COMMON_USER container=all;
grant select on v_$database to &P_COMMON_USER container=all;

/*Creating Application Seed Manually*/
create pluggable database as seed from &P_APPROOT_NAME file_name_convert=('&P_DB_MOUNTED_PATH/&P_CDB_NAME/&P_APPROOT_NAME/','&P_DB_MOUNTED_PATH/&P_CDB_NAME/SEED&P_APPROOT_NAME/');

alter pluggable database &P_APPROOT_NAME$SEED open;
alter session set container = &P_APPROOT_NAME$SEED;

@&P_PDB_TO_APPPDB;
select cause, type, message, status, action from pdb_plug_in_violations;

SET ERRORLOGGING OFF
SPOOL OFF