#!/bin/bash

pdb_name=$1
ts_name=${pdb_name^^}
USER=$ts_name
test -z $pdb_name && echo "*** help: missing new pdb_name" && exit
sqp="sqlplus -S sys/oracle as sysdba"
echo "show pdbs" | $sqp | grep ${pdb_name^^} && echo "*** pdb_name:${pdb_name} already exist ***" && exit


ora="/u01/app/oracle/oradata/ORACLE19"

$sqp <<EOF
create pluggable database $pdb_name ADMIN USER yurii IDENTIFIED BY password1
 FILE_NAME_CONVERT=('$ora/pdbseed', '$ora/$pdb_name/');
alter pluggable database $pdb_name open;
alter session set container=$pdb_name;
grant all privileges to yurii;

ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE OPEN UPGRADE;
ALTER SYSTEM SET max_string_size=extended;
@?/rdbms/admin/utl32k
ALTER PLUGGABLE DATABASE CLOSE;
ALTER PLUGGABLE DATABASE OPEN;
select parameter,value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET' or parameter='NLS_NCHAR_CHARACTERSET';
show parameter max_string_size;

CREATE SMALLFILE TABLESPACE "$ts_name" DATAFILE '$ora/$pdb_name/${ts_name}.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
CREATE USER "$USER" IDENTIFIED BY $USER DEFAULT TABLESPACE "$ts_name" TEMPORARY TABLESPACE TEMP;

GRANT CREATE SESSION,
CONNECT,
CREATE TABLE,
CREATE PROCEDURE,
CREATE TRIGGER,
CREATE VIEW,
CREATE SEQUENCE,
ALTER ANY TABLE,
ALTER ANY PROCEDURE,
ALTER ANY TRIGGER,
ALTER PROFILE,
INSERT ANY TABLE,
UPDATE ANY TABLE,
DELETE ANY TABLE,
DROP ANY TABLE,
DROP ANY PROCEDURE,
DROP ANY TRIGGER,
DROP ANY VIEW,
DROP PROFILE,
UNLIMITED TABLESPACE,
CREATE ANY DIRECTORY,
DROP ANY DIRECTORY TO "$USER";

quit
EOF

[ $? -eq 0 ] || echo "*FAIL*" && exit 1

echo "done."
