Useful Oracle Shell Aliases And SQL*Plus Settings

Inspired by Christian’s blog entry about rlwrap (german), I decided to share my functions and settings for Bourne Again shell and Oracle SQL*Plus.

Install rlwrap

rlwrap is needed to provide more history and ease of navigation at the SQL*Plus prompt. To install rlwrap — the readline wrapper — type on Mac OS X:

sudo port install rlwrap

Or Debian, Ubuntu, …

sudo aptitude install rlwrap

Or on Solaris using Blastwave:

sudo pkgutil install CSWrlwrap

.bash_login or .bashrc

Create a login profile for the oracle user in .bash_login or .bashrc and export some of Oracle’s environment variables. The shell prompt PS1 is set to include the actual ORACLE_SID — use apostrophe to include evnrionment variable in a prompt. Add some shell functions and aliases to provide ease of use:

  • setsid will set the ORACLE_SID including any needed environment variables like ORACLE_ADMIN
  • setbase sets ORACLE_BASE to the parameter; use with sethome!
  • sethome sets ORACLE_HOME to $ORACLE_BASE/product/ plus its parameter
  • sqlplus wraps SQL*Plus in rlwrap
  • sysdba calls sqlplus / as sysdba, already wrapped using rlwrap
  • sysoper calls sqlplus / as sysoper, already wrapped using rlwrap

The script:

# Oracle
_setpath() {
    if [ $# -gt 1 ]; then
        p=$(echo $PATH | sed "s#$2##")
    else
        p=$PATH
    fi
    export PATH=$1/bin:$p
}

_oracle_settnsadmin() {
    export TNS_ADMIN=$1
    _oracle_showenv
} 

_oracle_showenv() {
    env | grep -Ee "PATH|ORACLE|TNS|NLS" | sort
}

_oracle_setsid() {
    export ORACLE_SID=$1
    export ORACLE_ADMIN=$ORACLE_BASE/admin/$ORACLE_SID
    _oracle_showenv
}

_oracle_setbase() {
    export ORACLE_BASE=$1
    _oracle_showenv
    echo "** ATTENTION: Please set ORACLE_HOME with sethome too"
}

_oracle_sethome() {
    OLDORACLE_HOME=$ORACLE_HOME
    export ORACLE_HOME=$ORACLE_BASE/product/$1
    export ORACLE_ADMIN=$ORACLE_BASE/admin/$ORACLE_SID
    export TNS_ADMIN=$ORACLE_HOME/network/admin
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib
    _setpath $OLDORACLE_HOME $ORACLE_HOME
    _oracle_showenv
}

alias setsid='_oracle_setsid'
alias setbase='_oracle_setbase'
alias sethome='_oracle_sethome'
alias settns='_oracle_settnsadmin'
alias sqlplus='rlwrap sqlplus'
alias sysdba='sqlplus / as sysdba'
alias sysoper='sqlplus / as sysoper'
alias rman='rlwrap rman'

# Customize here
export PS1='\u@\h:\w ($ORACLE_SID) $ ' # Please don't use double-backslashes, use a single backslash!
setbase /u01/app/oracle >/dev/null
sethome 11.2.0/db_1 >/dev/null
setsid BLA1

Attention please don’t use double-backslashes in PS1 above, my blog just can’t display it correctly. Customize it for your system (e.g. LD_LIBRARY_PATH) or path in sethome.

Each command which changed the environment variable will print all variables belonging to Oracle in a sorted manner.

Example for setbase:

oracle:~ (BLA1) $ setbase /u01/app/oracle
LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/lib
ORACLE_ADMIN=/u01/app/oracle/admin/AOC
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0
ORACLE_SID=AOC
PATH=/bin:/usr/local/bin:/usr/bin:/bin:/usr/games
PS1=u@h:w ($ORACLE_SID) $ 
TNS_ADMIN=/u01/app/oracle/product/11.2.0/db_1/network/admin
** ATTENTION: Please set ORACLE_HOME with sethome too

Example for sethome:

oracle:~ (BLA1) $ sethome 11.2.0/db_1
LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/db_1/lib
ORACLE_ADMIN=/u01/app/oracle/admin/AOC
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
ORACLE_SID=AOC
PATH=/u01/app/oracle/product/11.2.0/db_1/bin:/bin:/usr/local/bin:/usr/bin:/bin:/usr/games
PS1=u@h:w ($ORACLE_SID) $ 
TNS_ADMIN=/u01/app/oracle/product/11.2.0/db_1/network/admin

As you can see, ORACLE_BASE, ORACLE_HOME, ORACLE_ADMIN, TNS_ADMIN and PATH were adjusted. When you change the SID:

oracle:~ (BLA1) $ setsid RCAT
LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/db_1/lib
ORACLE_ADMIN=/u01/app/oracle/admin/RCAT
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
ORACLE_SID=RCAT
PATH=/u01/app/oracle/product/11.2.0/db_1/bin:/bin:/usr/local/bin:/usr/bin:/bin:/usr/games
PS1=u@h:w ($ORACLE_SID) $ 
TNS_ADMIN=/u01/app/oracle/product/11.2.0/db_1/network/admin
oracle@arg0:~ (RCAT) $ 

the variables ORACLE_SID and ORACLE_ADMIN have changed.

Easy installation

Download my bash profile using wget or curl:

wget http://files.art-of-coding.eu/oracle/bashprofile.txt

Or include it directly into your .bash_profile like this:

wget -O - http://files.art-of-coding.eu/oracle/bashprofile.txt | cat >> .bash_profile

Example:

wget -O - http://files.art-of-coding.eu/oracle/bashprofile.txt | cat >> .bash_profile
--2011-02-22 11:26:27--  http://files.art-of-coding.eu/oracle/bashprofile.txt
Resolving files.art-of-coding.eu... 1.2.3.4
Connecting to files.art-of-coding.eu|1.2.3.4|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 824 [text language="/plain"][/text]
Saving to: `STDOUT'

100%[==========================================================================================>] 824         --.-K/s   in 0s      

2011-02-22 11:26:27 (70.3 MB/s) - `-' saved [824/824]

SQL*Plus prompt

Define a SQL*Plus prompt to see more than just SQL>. You can choose to create a login.sql which must be in the working directory where you execute sqlplus or put a glogin.sql in $ORACLE_HOME/sqlplus/admin:

set sqlprompt "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE&> "

You’ll now see where and what you are in SQL*Plus:

$ sysdba


SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 22 11:20:30 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning option

SYS@RCAT AS SYSDB> 

You can even add the actual date and time:

set termout off
alter session set nls_date_format = 'DD.MM. HH24:MI:SS';
set sqlprompt "_DATE _USER'@'_CONNECT_IDENTIFIER _PRIVILEGE> "
set termout on

$ sysdba
22.02. 11:22:17 SYS@RCAT AS SYSDBA> 

Resources

This entry was posted in Databases, System Administration and tagged , , . Bookmark the permalink.