Customize Linux and Oracle sqlplus prompt and Linux oracle and apps account environment setup

There are a few dot files in the OS account home directory (/home/applmgr or /home/oracle). The first one is .bash_profile which calls .bashrc which calls .profile_GOLD for some customized setting. .profile_GOLD calls user’s customization.

1. .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
unset USERNAME

2. .bashrc
In this file, I set the command history related settings. It is a good idea to have the command history timestamp.

# .bashrc

# User specific aliases and functions

# Source global definitions
if [ -f /etc/bashrc ]; then
. /etc/bashrc
fi
set -o vi
export HISTCONTROL=ignoredups
export HISTSIZE=9999
export HISTFILESIZE=999999
export HISTTIMEFORMAT="%F %T "
export EDITOR=vi
source $HOME/.profile_GOLD

3. .profile_PTCH
It is for Oracle applications. Use APPSPTCH_pind31.env rather APPSORA.env. For shared appl_top, these two files are different.

#.profile_PTCH
. /mnt/applptch/ptchappl/APPSPTCH_pind31.env

. /share/oracle/.me.ccmtier

For the database tier, use following:
#.profile_PTCH
. /opt/oracle/product/10.2.0/PTCH/PTCH2_pind22.env

. /share/oracle/.me.dbtier

4. .me.ccmtier
My preferred alias setting for reducing typing and os prompt for showing current working direct and time, login os user and hostname, instance name and apps servers.

#.me.ccmtier
##ALIAS
alias tns="cd $TNS_ADMIN"
alias itns="cd ${IAS_ORACLE_HOME}/network/admin/${CONTEXT_NAME}"
alias apacheconf="cd ${IAS_ORACLE_HOME}/Apache/Apache/conf"
alias apachelog="cd $IAS_ORACLE_HOME/Apache/Apache/logs"
alias jservetc="cd $IAS_ORACLE_HOME/Apache/Jserv/etc"
alias jservlog="cd $IAS_ORACLE_HOME/Apache/Jserv/logs"
alias oh="cd $ORACLE_HOME"
alias iasoh="cd $IAS_ORACLE_HOME"
alias admin="cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME"
alias commontop="cd $COMMON_TOP"
alias appltop="cd $APPL_TOP"
alias contextfile="cd $APPL_TOP/admin"
alias custop="cd $XX_CUSTOM_TOP"
alias mwa="cd $MWA_TOP/bin"
alias tomcat="cd $XXLPR_TOP/bin"

##PROMPT
export PS1="[\w] :[time \t]\n\u@\h(${TWO_TASK}/CCM:Admin):$ "

##Send X display to Linn's Desktop, run xming config first.
#export DISPLAY=172.18.33.73:0.0

5. .me.webtier
This one is for web tier. Difference is for os prompt.

#.me.webtier
##ALIAS
alias tns="cd $TNS_ADMIN"
alias itns="cd ${IAS_ORACLE_HOME}/network/admin/${CONTEXT_NAME}"
alias apacheconf="cd ${IAS_ORACLE_HOME}/Apache/Apache/conf"
alias apachelog="cd $IAS_ORACLE_HOME/Apache/Apache/logs"
alias jservetc="cd $IAS_ORACLE_HOME/Apache/Jserv/etc"
alias jservlog="cd $IAS_ORACLE_HOME/Apache/Jserv/logs"
alias oh="cd $ORACLE_HOME"
alias iasoh="cd $IAS_ORACLE_HOME"
alias admin="cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME"
alias commontop="cd $COMMON_TOP"
alias appltop="cd $APPL_TOP"
alias contextfile="cd $APPL_TOP/admin"
alias custop="cd $XX_CUSTOM_TOP"
alias mwa="cd $MWA_TOP/bin"
alias tomcat="cd $XXLPR_TOP/apache-tomcat-4.1.36/bin"

##PROMPT
export PS1="[\w] :[time \t]\n\u@\h(${TWO_TASK}/Web:Forms:MWA):$ "

##Send X display to Linn's Desktop, run xming config first.
#export DISPLAY=172.18.33.73:0.0


6. .me.dbtier

#.me.dbtier

##ALIAS
alias tns="cd $TNS_ADMIN"
alias bdump="cd $ORACLE_HOME/admin/$CONTEXT_NAME/bdump"
alias udump="cd $ORACLE_HOME/admin/$CONTEXT_NAME/udump"
alias oh="cd $ORACLE_HOME"
alias dbs="cd $ORACLE_HOME/dbs"
alias admin="cd $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME"
alias contextfile="cd $ORACLE_HOME/appsutil"

##PROMPT
export PS1="[\w] :[time \t]\n\u@\h(${ORACLE_SID}/DBTier):$ "

##Send X display to Linn's Desktop, run xming config first.
#export DISPLAY=172.18.33.73:0.0

7. examples

[/opt/oracle/product/10.2.0/PTCH]
oracle@pind21(PTCH1/DBTier):$

[/mnt/applptch/ptchappl]
applptch@pind31(PTCH_806_BALANCE/CCM:Admin):$

[/mnt/applptch/ptchappl]
applptch@pind33(PTCH_806_BALANCE/Web:Forms:MWA):$

8. customize database sqlplus prompt

add following to the bottom of glogin.sql in $ORACLE_HOME/sqlplus/admin (client or server side) to customized sqlplus prompt. It is very convenient at client side as we might connect to different database.

set lines 132
set pages 100
set termout off
col dbname new_value prompt_dbname
select instance_name dbname from v$instance;
set sqlprompt "_USER@&&prompt_dbname>"
set termout on
set time on