Sqlplus Shortcut Script

usage: /home/oracle/DV/bin/sp [-s ORACLE_SID] <id>

Connecting to joe as HR...

SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 20 12:40:13 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected.

joe              UN: joe                            linux7vm.zanenet                         12.2.0.1.0

USER is "HR"

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

SQL>

-----------------------------  Begin Script "sp" ----------------------------

#!/bin/bash
#
#    Requires:
#        - User with DBA role
#        - Password for that user available under ~/.x   in the form of <user>/<password>
#
#

homedir=~/DV
mkdir -p $homedir/tmp
confile=$homedir/tmp/sp_con2.tmp
outfile=$homedir/tmp/sp_out2.tmp
remoteuser=DVDBA

usage ()
{
print "\nusage: ${0##$/} [-s ORACLE_SID] <id> \n"
print "           -s ORACLE_SID  will log into ORACLE_SID as $remoteuser"
print "           <id>  Log in as this id\n"
print "\n"
exit
}

set_grant ()
{
sqlplus -s /nolog >$outfile <<_E
set echo on
set pages 0
connect $CONNECT_STR
alter user $id grant connect through $remoteuser ;
exit;
_E

if [[ `grep -c "ORA-" $outfile ` -gt 0 ]]; then
printf "\n\tError in Setting Grant:\n"
cat $outfile
exit
fi

}

id=SYS
sid=$ORACLE_SID
if [[ $# -gt 3 ]]; then usage; fi
while [[ $# -gt 0 ]]; do
case $1 in
-s ) shift; sid=`echo $1 | tr '[a-z]' '[A-Z]'`; shift;;
?|help|HELP|-h|-?) usage;;
*) id=`echo $1| tr '[a-z]' '[A-Z]'`; shift;;
esac
done

pass=`awk '{print $1}' $homedir/bin/.x |cut -d/ -f2`

CONNECT_STR="${remoteuser}/${pass}"

if [[ $sid = NONE ]]; then echo "\nduh.  NONE? You can not connect to that...\n\n"; exit 1; fi
if [[ $sid = $ORACLE_SID ]] && [[ $id = "SYS" ]]; then
CONNECT_STR="/ as sysdba"
elif [[ $sid = $ORACLE_SID ]] && [[ ! $id = "SYS" ]]; then
set_grant;
CONNECT_STR="${remoteuser}[$id]/$pass"
elif [[ ! $sid = $ORACLE_SID ]] && [[ $id = "SYS" ]]; then
CONNECT_STR="${remoteuser}/${pass}@${sid}"
id=${remoteuser}
else
CONNECT_STR=${CONNECT_STR}@${sid}
set_grant;
CONNECT_STR="${remoteuser}[$id]/${pass}@${sid}"
fi

echo "Connecting to $sid as $id..."

cat <<_E >$confile
connect $CONNECT_STR
set lines 150
set pages 0
set serverout on
col host_name for a40
prompt
select '                     '||instance_name, 'UN: ' ||db_unique_name, host_name, version from v\$instance, v\$database;
show user;
prompt --------------------------------;
set pages 200;
prompt
_E
sqlplus /nolog @$confile

rm -f $confile  $outfile

-----------------------------  End Script "sp" ----------------------------