Demo Schema Script not updated for Container Database in 12c

This afernoon i was installing OE/HR Demo script supplied with Oracle 12c Binaries. When I try to install OE schema into one of my pluggable Database PDBOE01 it could not complete successfully as script are not compatible with Container/Pluggable database architecture.


you must modify the script before running otherwise you will end up with lots of garbage stuff into your root container database.


You will notice in the log1, user CREATED successfully and then grant failed to find the user created as its looking into the wrong database(root container) etc etc.

Its causing issue because script still connecting to database without TNS string and hence it connect to CDB everytime its re-connect while running the script.

cd /u01/app/oracle/product/12.1.0/dbhome_1/demo/schema/order_entry

$ grep -i  -e 'CONNECT SYS/' -e  'CONNECT OE/' *.sql
coe_xml.sql:CONNECT sys/&pass_sys AS SYSDBA;
coe_xml.sql:CONNECT oe/&pass_oe;
coe_xml.sql:CONNECT sys/&pass_sys AS SYSDBA;
coe_xml.sql:CONNECT oe/&&pass_oe;
createUser.sql:CONNECT OE/&pass_oe
createUser.sql:CONNECT sys/&&pass_sys AS SYSDBA;
createUser.sql:CONNECT OE/&pass_oe
oe_main.sql:CONNECT sys/&pass_sys AS SYSDBA;
oe_main.sql:CONNECT oe/&pass
oe_main.sql:CONNECT sys/&pass_sys AS SYSDBA;
xdb03usg.sql:CONNECT sys/&&pass_sys AS SYSDBA;
xdb03usg.sql:CONNECT OE/&pass_oe

all of above need to updated before running against pluggable database .

Simple script to update all to point to my Pluggable database PDBOE01 i used the following.

for i in `ls`

do

sed -i 's/CONNECT sys\//CONNECT SYS@PDBOE1\//g' $i

sed -i 's/CONNECT oe\//CONNECT oe@PDBOE1\//g' $i

sed -i 's/CONNECT OE\//CONNECT oe@PDBOE1\//g' $i

done

where PDBOE01 is pluggable database TNS entry.

If feel this all could be done with single SED command , comments welcome for suggestion on that.

now all files updated with TNS entry. 

coe_xml.sql:CONNECT SYS@PDBOE1/&pass_sys AS SYSDBA;

coe_xml.sql:CONNECT oe@PDBOE1/&pass_oe;

coe_xml.sql:CONNECT SYS@PDBOE1/&pass_sys AS SYSDBA;

coe_xml.sql:CONNECT oe@PDBOE1/&&pass_oe;

createUser.sql:CONNECT oe@PDBOE1/&pass_oe

createUser.sql:CONNECT SYS@PDBOE1/&&pass_sys AS SYSDBA;

createUser.sql:CONNECT oe@PDBOE1/&pass_oe

oe_main.sql:CONNECT SYS@PDBOE1/&pass_sys AS SYSDBA;

oe_main.sql:CONNECT oe@PDBOE1/&pass

oe_main.sql:CONNECT SYS@PDBOE1/&pass_sys AS SYSDBA;

xdb03usg.sql:CONNECT SYS@PDBOE1/&&pass_sys AS SYSDBA;

xdb03usg.sql:CONNECT oe@PDBOE1/&pass_oe

----------------------- Log1 of run without update (Failed ) -----------------------
SQL> @oe_main.sql

specify password for OE as parameter 1:
Enter value for 1: oracle

specify default tablespeace for OE as parameter 2:
Enter value for 2: OEHR_DATA

specify temporary tablespace for OE as parameter 3:
Enter value for 3: TEMP

specify password for HR as parameter 4:
Enter value for 4: oracle

specify password for SYS as parameter 5:
Enter value for 5: oracle

specify directory path for the data files as parameter 6:
Enter value for 6:
SP2-0137: DEFINE requires a value following equal sign

writeable directory path for the log files as parameter 7:
Enter value for 7:
SP2-0137: DEFINE requires a value following equal sign

specify version as parameter 8:
Enter value for 8: v3

Enter value for log_path:

User dropped.

old   1: CREATE USER oe IDENTIFIED BY &pass
new   1: CREATE USER oe IDENTIFIED BY oracle

User created.

old   1: ALTER USER oe DEFAULT TABLESPACE &tbs QUOTA UNLIMITED ON &tbs
new   1: ALTER USER oe DEFAULT TABLESPACE OEHR_DATA QUOTA UNLIMITED ON OEHR_DATA

User altered.

old   1: ALTER USER oe TEMPORARY TABLESPACE &ttbs
new   1: ALTER USER oe TEMPORARY TABLESPACE TEMP

User altered.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.

Connected.
GRANT execute ON sys.dbms_stats TO oe
                                   *
ERROR at line 1:
ORA-01917: user or role 'OE' does not exist


ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0640: Not connected
SP2-0640: Not connected

specify Sample Schema version as parameter 1:

specify password for OE as parameter 2:

PROMPT password for SYS as parameter 3:

SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected

----------------------------------------------------