This script uses the local tnsnames.ora file to create a connection, and requires the variable ${p} to hold the current SYSTEM password.
#on récupère le mdp systeme
[[ -z "${p}" ]] && echo "First set the system password into variable ${p}" && exit 1
for tns in `cat /${ORACLE_HOME}/network/admin/tnsnames.ora | grep -i ^${1} | cut -d= -f1`
do
#on récupère l'environnment
case ${tns} in
*PRD) loke="ALTER USER **** ACCOUNT ${2:-LOCK};";;
*) loke="ALTER USER **** ACCOUNT UNLOCK;";;
esac
states="
CREATE USER xxx;
-- add privs
GRANT *** TO xxx;
"
#fin du statement , on se connecte à la base et on exécute les 2 scripts sql.
echo ${tns}
sqlplus -s /nolog <<EOF
connect system/${p}@${tns}
select global_name from global_name;
${states}
${loke}
quit
EOF
done