Select Page

Sqlplus Shortcut Script

Zane Warton | | 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?

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

ORA-12154: TNS:could not resolve the connect identifier specified

Most people will encounter this error when their application tries to connect to an Oracle database service, but it can also be raised by one database instance trying to connect to another database service via a database link.

Jeremiah Wilton | March 4, 2009

12c Upgrade Bug with SQL Tuning Advisor

Learn the steps to take on your Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Best RAID For SQL Server | RAID 0, RAID 1, RAID 5, RAID 10

Which RAID should you use with SQL Server? Learn the differences between RAID 0, RAID 1, RAID 5, and RAID 10, along with best practices.

Eric Russo | June 8, 2015

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS