Sqlplus Shortcut Script
Author: Zane Warton | 5 min read | June 25, 2019
Sqlplus shortcut script (Automation)
I’ve created the following script named “sp” for a few purposes. One to provide a shortcut for the thousands of times a week I enter “sqlplus / as sysdba.” Second, it takes advantage of Oracle’s login by proxy mechanic to allow a DBA user account to login as any user without the need to change or know the password. This is especially useful when trying to troubleshoot Oracle permissions issues. Third, with the -s option, you can connect with your default user with any database set up in tnsnames.ora
Examples of use/usage statement:
$ sp -?
usage: /home/oracle/DV/bin/sp [-s ORACLE_SID] <id>
-s ORACLE_SID will log into ORACLE_SID as DVDBA <id> Log in as this id
Connecting to the local instance as the “HR” user:
$ sp hr
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" ----------------------------
And there you have it. Did it work for you?
