This script is short version of the creating Oracle Database Objects like Users, Profiles, Roles and Tablespace using single shell script. Usually this script can help to onboard customers quickly and you can integrate in automation.

Really good thing about this script since I have written this script using plsql it will checks if objects exists in database. If Objects exists it will let know objects exists or else it creates.

If you are using CDB/PDB Oracle Databases then there is small trick who I got know during this scripting which “EXECUTE IMMEDIATE ‘ALTER session set “_ORACLE_SCRIPT”=true’;” you need to execute this session before you trying to create the users in tablespace.

#!/usr/bin/ksh
# November 2022 Sandeep Reddy Narani
# Interactive database picker
# Displays menu of databases defined in oratab then asks which one to use then uses oraenv to set env variables
#
export ORACLE_SID=$1
export ORACLE_HOME=`grep "^${ORACLE_SID}:" /etc/oratab|cut -d: -f2 -s`
export PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export SERVICE_NAME=${2}
export CLIENT_NAME=`echo $SERVICE_NAME | cut -c 3-`
export username=${CLIENT_NAME}_ADMIN
export CDATE=`date '+%Y%m%d_%H%M'`
export LOGFILE=/home/oracle/${SCRIPT_NAME}_${SERVICE_NAME}_${CDATE}.log

if [ $# -ne 2 ]
then
    echo "Usage: create_tablespaces_schemas.sh CDB PDB_Service_NAME CLIENT_acronym"
        exit 1
fi

echo  "CREATING TABLESPACES USERS ${USERNAME} in pluggable database: ${SERVICE_NAME} in container database: ${ORACLE_SID}"
$ORACLE_HOME/bin/sqlplus -s system/@${DB} <<-EOF
alter session set container=${SERVICE_NAME};
select to_char(sysdate,'RRRRMMDD_HH24MISS') dcol from dual;
SET FEEDBACK OFF SERVEROUTPUT ON VERIFY OFF TERMOUT OFF
spool creation_spool.log
set verify off

-- echo "Check & Create Temp Tablespace ${CLIENT_NAME}_ARTMP in in pluggable database: ${SERVICE_NAME} in container database: ${ORACLE_SID}"
-- Create temp tablespace if it doesnt exist
declare
  t_count number;
begin
  select count(*)
    into t_count
  from dba_tablespaces
  where tablespace_name ='${CLIENT_NAME}_ARTMP';
  DBMS_OUTPUT.put_line('Temp Tablespaces Count:' || t_count);
  if t_count = 0
  then
     DBMS_OUTPUT.put_line ('     ');
     DBMS_OUTPUT.put_line ('=> Creating tablespace ${CLIENT_NAME}_ARTMP ');
     execute immediate 'CREATE TEMPORARY TABLESPACE ${CLIENT_NAME}_ARTMP TEMPFILE ''+DATA'' SIZE 10g AUTOEXTEND ON NEXT 1024M MAXSIZE 32767M';
     DBMS_OUTPUT.put_line ('     ');
     DBMS_OUTPUT.put_line ('=> Tablespace ${CLIENT_NAME}_ARTMP created successfully');
     DBMS_OUTPUT.put_line ('     ');
  end if;
end;
/

-- echo "Check & Create Undo Tablespace UNDOTBS1 in in pluggable database: ${SERVICE_NAME} in container database: ${ORACLE_SID}"

-- Create undo tablespace if it doesnt exist
declare
  u_count number;
begin
  select count(*)
    into u_count
  from dba_tablespaces
  where tablespace_name ='UNDOTBS1';
  DBMS_OUTPUT.put_line('Undo Tablespaces Count:' || u_count);
  if u_count = 0
  then
     DBMS_OUTPUT.put_line ('     ');
     DBMS_OUTPUT.put_line ('=> Creating tablespace UNDOTBS1 ');
     execute immediate 'CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE ''+DATA'' SIZE 10g AUTOEXTEND ON NEXT 1024M MAXSIZE 32767M';
     DBMS_OUTPUT.put_line ('     ');
     DBMS_OUTPUT.put_line ('=> Tablespace UNDOTBS1 created successfully');
     DBMS_OUTPUT.put_line ('     ');
  end if;
end;
/

-- echo "Check & Create AR Tablespace ${CLIENT_NAME}_AR in in pluggable database: ${SERVICE_NAME} in container database: ${ORACLE_SID}"

-- Create default ar tablespace if it doesnt exist
declare
  l_count number;
begin
  select count(*)
    into l_count
  from dba_tablespaces
  where tablespace_name ='${CLIENT_NAME}_AR';
  DBMS_OUTPUT.put_line('AR Tablespaces Count:' || l_count);
  if l_count = 0
  then
     DBMS_OUTPUT.put_line ('     ');
     DBMS_OUTPUT.put_line ('=> Creating tablespace ${CLIENT_NAME}_AR ');
     execute immediate 'CREATE BIGFILE TABLESPACE ${CLIENT_NAME}_AR DATAFILE ''+DATA'' SIZE 50G AUTOEXTEND ON next 1g MAXSIZE 4500G';
     DBMS_OUTPUT.put_line ('     ');
     DBMS_OUTPUT.put_line ('=> Tablespace ${CLIENT_NAME}_AR created successfully');
     DBMS_OUTPUT.put_line ('     ');
  end if;
end;
/

alter session set container=${SERVICE_NAME};
-- Creating Oracle Profile
-- Creating Oracle Profile
-- echo "Check & Create Profile PROFILE_${CLIENT_NAME} in in pluggable database: ${SERVICE_NAME} in container database: ${ORACLE_SID}"

declare
   p_count number;
begin
   select count(*) into p_count from dba_profiles where profile='PROFILE_${CLIENT_NAME}' AND RESOURCE_NAME='COMPOSITE_LIMIT';
   DBMS_OUTPUT.put_line('profile Count:' || p_count);
   if p_count = 0
   then
      DBMS_OUTPUT.put_line ('     ');
      execute immediate 'create profile PROFILE_${CLIENT_NAME} limit sessions_per_user 10 cpu_per_session unlimited cpu_per_call 6000 logical_reads_per_session unlimited logical_reads_per_call 100 idle_time 30 connect_time 480';
      DBMS_OUTPUT.put_line ('     ');
      DBMS_OUTPUT.put_line ('=> Profile PROFILE_${CLIENT_NAME} created successfully');
      DBMS_OUTPUT.put_line ('     ');
   end if;
 end;
 /


alter session set container=${SERVICE_NAME};
-- Creating Oracle Admin Roles
-- echo "Check & Create Admin Roles ${CLIENT_NAME}_ADMIN in in pluggable database: ${SERVICE_NAME} in container database: ${ORACLE_SID}"

declare
   ra_count number;
begin
   select count(*) into ra_count from dba_roles where role='${CLIENT_NAME}_ADMIN';
   DBMS_OUTPUT.put_line('Role Count:' || ra_count);
   if ra_count = 0
   then
      DBMS_OUTPUT.put_line ('     ');
      EXECUTE IMMEDIATE 'ALTER session set "_ORACLE_SCRIPT"=true';
      execute immediate 'create role ${CLIENT_NAME}_ADMIN';
      DBMS_OUTPUT.put_line ('     ');
       -- Grant permissions
      EXECUTE IMMEDIATE ('GRANT create session TO ${CLIENT_NAME}_ADMIN');
      EXECUTE IMMEDIATE ('GRANT connect TO ${CLIENT_NAME}_ADMIN');
      EXECUTE IMMEDIATE ('GRANT alter session, create session, create synonym, create view, create table, create cluster, create database link TO ${CLIENT_NAME}_ADMIN');
      EXECUTE IMMEDIATE ('GRANT create sequence,create procedure,create trigger,query rewrite TO ${CLIENT_NAME}_ADMIN');
      DBMS_OUTPUT.put_line ('=> Role ${CLIENT_NAME}_ADMIN created successfully');
      DBMS_OUTPUT.put_line ('     ');
   end if;
 end;
 /

alter session set container=${SERVICE_NAME};

declare
   dbb_count number;
begin
   select count(*) into dbb_count from dba_users where username='DBBACKUPADMIN';
   DBMS_OUTPUT.put_line('DBBackup Admin user Count:' || dbb_count);
   if dbb_count = 0
   then
      DBMS_OUTPUT.put_line ('     ');
      EXECUTE IMMEDIATE 'ALTER session set "_ORACLE_SCRIPT"=true';
      EXECUTE IMMEDIATE 'CREATE USER DBBACKUPADMIN IDENTIFIED BY "###" default tablespace users quota 20m on users';
      DBMS_OUTPUT.put_line ('     ');
      DBMS_OUTPUT.put_line ('=> User Created DBBACKUPADMIN created successfully');
      DBMS_OUTPUT.put_line ('     ');
        -- Grant permissions
    EXECUTE IMMEDIATE ('GRANT create session TO DBBACKUPADMIN');
    EXECUTE IMMEDIATE ('GRANT connect TO DBBACKUPADMIN');
    EXECUTE IMMEDIATE ('GRANT alter session, create session, create synonym, create view, create table, create cluster, create database link TO DBBACKUPADMIN');
    EXECUTE IMMEDIATE ('GRANT create session, connect, resource, DATAPUMP_EXP_FULL_DATABASE,DATAPUMP_IMP_FULL_DATABASE TO DBBACKUPADMIN');
   end if;
 end;
 /
EOF
exit;