Locks in Oracle by Example

In Short How to find a lock on a table

SELECT 

'ALTER SYSTEM KILL SESSION '''||V$SESSION.sid||','||V$SESSION.serial#||''' IMMEDIATE; ' AS kill_command, 

  a.session_id,       

  a.oracle_username, 

  a.os_user_name,

  b.owner "OBJECT OWNER",  

  b.object_name,   

  b.object_type,

  a.locked_mode 

 FROM (SELECT object_id, session_id, oracle_username, os_user_name, locked_mode

        FROM V$LOCKED_OBJECT) a, 

  (SELECT object_id, owner, object_name,object_type      

     FROM DBA_OBJECTS) b,

  V$SESSION

WHERE a.object_id=b.object_id

  AND a.session_id = V$SESSION.sid;

This is working, tested!

SESSION_ID ORACLE_USERNAME  OS_USER_NAME  OBJECT OWNER  OBJECT_NAME   OBJECT_TYPE  LOCKED_MODE
---------- ---------------- ------------- ------------- ------------- ------------ -----------
       592 ORA_USER         linux_user    ORA_USER      TABLE_A       TABLE                  3
       592 ORA_USER         linux_user    ORA_USER      TABLE_B       TABLE                  0
       441 ORA_USER         linux_user    ORA_USER      TABLE_B       TABLE                  6

V$LOCKED_OBJECT Oracle Reference
lock mode 
0 -  lock requested but not yet obtained
3 - ROW_X (SX): Row Exclusive Table Lock
6 - Exclusive (X): Exclusive Table Lock


General
Update same table from two sessions
How to find the locking and the locked session?






Flow
Run UPDATE statement without commit, and find the uncommitted session.


From sqlplus:
SQL> UPDATE ADDRESS SET addr_zip_code = '21213' WHERE addr_id = 1;
> no commit!!

SELECT * FROM V$SESSION 
 WHERE username = 'ALEC_USER' 
  AND program = 'sqlplus.exe'

The sid and serial# are:
sid: 205
serial#: 21296


==========================
Relate Locking Session ID to SQL Text
==========================
SQL Text could be found in several tables:

V$SQLAREA
V$SQLTEXT
V$SQLTEXT_WITH_NEWLINES

--Ignore INACTIVE Sessions
SELECT SESSIONS.sid||'-'|| SESSIONS.serial# AS SID_SERIAL#, 
       SESSIONS.username, 
       SESSIONS.osuser, 
       SESSIONS.machine, 
       SESSIONS.program, 
       SESSIONS.module, 
       REPLACE(SQLTEXT.sql_text,CHR(10),'') sql_text       
FROM V$SQLAREA SQLTEXT,
     V$SESSION SESSIONS
WHERE SQLTEXT.address = SESSIONS.sql_address
  AND SQLTEXT.hash_value = SESSIONS.prev_hash_value
  AND SESSIONS.status != 'INACTIVE'  --Ignore only INACTIVE Sessions
  AND SESSIONS.audsid <> USERENV('sessionid') --Ignore current session
ORDER BY SQLTEXT.address

--For INACTIVE Sessions
SELECT SESSIONS.sid||'-'|| SESSIONS.serial# AS SID_SERIAL#, 
       SESSIONS.username, 
       SESSIONS.osuser, 
       SESSIONS.machine, 
       SESSIONS.program, 
       SESSIONS.module, 
       REPLACE(SQLTEXT.sql_text,CHR(10),'') sql_text       
FROM V$SQLAREA SQLTEXT,
     V$SESSION SESSIONS
WHERE SQLTEXT.address = SESSIONS.prev_sql_addr
  AND SQLTEXT.hash_value = SESSIONS.prev_hash_value
  AND SESSIONS.status = 'INACTIVE'
ORDER BY SQLTEXT.address

==========================
Find locking and locked sessions
==========================
Option A. 
Use V$LOCK table as parent/child relation. 

block-1 - blocking others

block-0 - not blocking others

SELECT           
         LOCKING.SID AS LOCKING_SID,           
         LOCKED.SID AS LOCKED_SID,
         LOCKING.type
  FROM   V$LOCK LOCKING,
         V$LOCK LOCKED
  WHERE 1=1
    AND LOCKING.block=1
    AND LOCKED.block=0
    AND LOCKING.ID1||LOCKING.ID2 = LOCKED.ID1||LOCKED.ID2;


LOCKING_SID LOCKED_SID TYPE
----------- ---------- ----
        194        205 TX


Elapsed: 00:04:00.03


Option B.
Use UNION ALL between V$LOCK LOCKS and V$LOCK LOCKED.
This one runs much faster, approx 2 seconds.

SELECT LOCKED_SESSIONS.lock_status,

       LOCKED_SESSIONS.sid,    

       LOCKED_SESSIONS.type,

       SESSIONS.sid||','|| SESSIONS.serial# AS SID_SERIAL#, SESSIONS.username, osuser, machine, program, SESSIONS.module,       

       SUBSTR(REPLACE(SQLTEXT.sql_text,CHR(10),''),0,400) AS SQL_TEXT

FROM(    

    SELECT 

       DECODE(LOCKING.block,0,'Not Locking','Locking') AS LOCK_STATUS,

       LOCKING.SID AS SID,

       LOCKING.type, 

       LOCKING.ID1, 

       LOCKING.ID2, 

       LOCKING.lmode, 

       LOCKING.request, 

       LOCKING.block

    FROM   V$LOCK LOCKING

    WHERE 1=1

      AND LOCKING.block=1

   UNION ALL

    SELECT 

       DECODE(LOCKED.block,0,'Not Locking','Locking') AS LOCK_STATUS,

       LOCKED.SID AS SID,           

       LOCKED.type, 

       LOCKED.ID1, 

       LOCKED.ID2, 

       LOCKED.lmode, 

       LOCKED.request, 

       LOCKED.block

    FROM   V$LOCK LOCKED

    WHERE 1=1

      AND LOCKED.block=0

      AND ID1||ID2 IN

         (SELECT ID1||ID2 FROM V$LOCK WHERE V$LOCK.block = 1)

)LOCKED_SESSIONS,

 V$SESSION SESSIONS, 

 V$SQLAREA SQLTEXT

WHERE SESSIONS.sid = LOCKED_SESSIONS.sid

  --AND SESSIONS.username = 'ALEC_USER' 

  --AND SESSIONS.program = 'sqlplus.exe'

  AND SQLTEXT.address = SESSIONS.prev_sql_addr

  AND SQLTEXT.hash_value = SESSIONS.prev_hash_value

ORDER BY 1

LOCK_STATUS  LOCK_ID1_ID2     SID TYPE SID_SERIAL USERNAME   OSUSER     MACHINE       
------------ --------------- ---- ---- ---------- ---------- ---------- ------------- 
OK           196623-379112    194 TX   194-9665   ALEC_USER  akaplan    1\ALEC-KAPLAN 
Being Locked 196623-379112    205 TX   205-21296  ALEC_USER  akaplan    1\ALEC-KAPLAN 


PROGRAM     MODULE     SQL_TEXT
----------- ---------- ----------------------------------------
sqlplus.exe SQL*Plus   commit
sqlplus.exe SQL*Plus   UPDATE ADDRESS SET addr_zip_code = '212

Elapsed: 00:00:02.00



==========================
Find locking and locked Objects
==========================
SELECT LOCKED_SESSIONS.lock_status,
  id1||'-'||id2 LOCK_ID1_ID2,
  LOCKED_SESSIONS.sid,    
  LOCKED_SESSIONS.type,
  SESSIONS.sid||'-'|| SESSIONS.serial# AS SID_SERIAL#, 
  SESSIONS.username, 
  SESSIONS.osuser, 
  SESSIONS.machine, 
  SESSIONS.program, 
  SESSIONS.module,
  OBJECTS.owner,
  OBJECTS.object_name,
  OBJECTS.object_type AS TYPE,
  LOCKED_OBJECTS.process,
  PROCESS.spid
FROM(    
     SELECT 
  DECODE(BEING_LOCKED.block,0,'OK','Being Locked') AS LOCK_STATUS,
  BEING_LOCKED.SID AS SID,
  BEING_LOCKED.type, 
  BEING_LOCKED.ID1, 
  BEING_LOCKED.ID2, 
  BEING_LOCKED.lmode, 
  BEING_LOCKED.request, 
  BEING_LOCKED.block
     FROM   V$LOCK BEING_LOCKED
     WHERE 1=1
       AND BEING_LOCKED.block=1
    UNION ALL
     SELECT 
  DECODE(LOCKS.block,0,'OK','Being Locked') AS LOCK_STATUS,            
  LOCKS.SID AS SID,           
  LOCKS.type, 
  LOCKS.ID1, 
  LOCKS.ID2, 
  LOCKS.lmode, 
  LOCKS.request, 
  LOCKS.block
     FROM   V$LOCK LOCKS
     WHERE 1=1
       AND LOCKS.block=0
       AND ID1||ID2 IN
          (SELECT ID1||ID2 FROM V$LOCK BEING_LOCKED WHERE BEING_LOCKED.block = 1)
       AND LOCKS.block=0
  )LOCKED_SESSIONS,
   V$SESSION SESSIONS, 
   DBA_OBJECTS OBJECTS,
   V$LOCKED_OBJECT LOCKED_OBJECTS,
   V$PROCESS PROCESS 
WHERE SESSIONS.sid = LOCKED_SESSIONS.sid
  AND LOCKED_SESSIONS.sid = LOCKED_OBJECTS.session_id
  AND OBJECTS.object_id = LOCKED_OBJECTS.object_id
  AND PROCESS.addr = SESSIONS.paddr;

LOCK_STATUS  LOCK_ID1_ID2           SID TYPE       SID_SERIAL USERNAME   OSUSER     
------------ --------------- ---------- ---------- ---------- ---------- ---------- 
OK           196623-379112          194 TX         194-9665   ALEC_USER  akaplan   
Being Locked 196623-379112          205 TX         205-21296  ALEC_USER  akaplan   
Being Locked 196623-379112          205 TX         205-21296  ALEC_USER  akaplan   

MACHINE       PROGRAM     MODULE     OWNER      OBJECT_NAME     TYPE   PROCESS   SPID
------------- ----------- ---------- ---------- --------------- ------ --------- -----
 1\ALEC-KAPLAN sqlplus.exe SQL*Plus   ALEC_USER  ADDRESS         TABLE 4052:2348 30417
 1\ALEC-KAPLAN sqlplus.exe SQL*Plus   ALEC_USER  ADDRESS         TABLE 2176:3308 20300
 1\ALEC-KAPLAN sqlplus.exe SQL*Plus   ALEC_USER  MLOG$_ADDRESS   TABLE 2176:3308 20300

The SPID column is the Server Process ID
The PROCESS column is the Client Process ID:Thread ID
In this example, the Client is on Windows.

SPID - is the Process on Oracle server:
oracle@my_server:~>% ps -ef | grep oracle | grep 30417 | grep -v grep
oracle   30417     1  0 09:12 ?        00:00:00 oracleigt (LOCAL=NO)

oracle@my_server:~>% ps -ef | grep 20300 | grep -v grep
oracle   20300     1  0 Oct05 ?        00:00:01 oracleigt (LOCAL=NO)


The PROCESS column is the Client Process ID:Thread ID
In this example, the Windows Process:Thread.

This is the Process running on the Windows Client, as captured with Process Explorer.
The locking process is 4052:2348, i.e. process 4052, thread:2348
The locked process is 2176:3308, i.e. process 2176, thread:3308

=================

V$TRANSACTION Table
=================

V$TRANSACTION Table holds additional info regarding the locked and the locking transactions.

SELECT LOCKED_SESSIONS.lock_status,

       id1||'-'||id2 LOCK_ID1_ID2,
       LOCKED_SESSIONS.sid,     
       LOCKED_SESSIONS.type,
       SESSIONS.sid||'-'|| SESSIONS.serial# AS SID_SERIAL#, 
       SESSIONS.username, SESSIONS.osuser, SESSIONS.machine, 
       SESSIONS.program, SESSIONS.module,
       OBJECTS.owner,
       OBJECTS.object_name,
       OBJECTS.object_type,
       LOCKED_OBJECTS.process,
       PROCESS.spid,
       TO_CHAR(TRANSACTION.start_date,'DD/MM/YYYY hh24:mm:ss') AS TX_START_DATE,
       TRANSACTION.status AS TX_STATUS,
       TRANSACTION.start_scn
    FROM(     
    SELECT 
         DECODE(BEING_LOCKED.block,0,'OK','Being Locked') AS LOCK_STATUS,
         BEING_LOCKED.SID AS SID,
         BEING_LOCKED.type, 
         BEING_LOCKED.ID1, 
         BEING_LOCKED.ID2, 
         BEING_LOCKED.lmode, 
         BEING_LOCKED.request, 
         BEING_LOCKED.block
    FROM V$LOCK BEING_LOCKED
    WHERE 1=1
      AND BEING_LOCKED.block=1
    UNION ALL
    SELECT 
         DECODE(LOCKS.block,0,'OK','Being Locked') AS LOCK_STATUS,            
         LOCKS.SID AS SID,           
         LOCKS.type, 
         LOCKS.ID1, 
         LOCKS.ID2, 
         LOCKS.lmode, 
         LOCKS.request, 
         LOCKS.block
    FROM V$LOCK LOCKS
    WHERE 1=1
      AND LOCKS.block=0
      AND ID1||ID2 IN (SELECT ID1||ID2 FROM V$LOCK BEING_LOCKED WHERE BEING_LOCKED.block = 1)
    AND LOCKS.block=0
    )LOCKED_SESSIONS,
V$SESSION SESSIONS, 
DBA_OBJECTS OBJECTS,
V$LOCKED_OBJECT LOCKED_OBJECTS,
V$PROCESS PROCESS,
V$TRANSACTION TRANSACTION
WHERE SESSIONS.sid = LOCKED_SESSIONS.sid
  AND LOCKED_SESSIONS.sid = LOCKED_OBJECTS.session_id
  AND SESSIONS.username = 'ALEC_USER' 
  AND SESSIONS.program = 'sqlplus.exe'
  AND OBJECTS.object_id = LOCKED_OBJECTS.object_id
  AND PROCESS.addr = SESSIONS.paddr
  AND SESSIONS.saddr = TRANSACTION.ses_addr

LOCK_STATUS LOCK_ID1_ID2 SID TYPE SID_SERIAL USERNAME OSUSER MACHINE PROGRAM MODULE
------------ ------------- --- ---- ---------- --------- ------- ------------- ----------- --------
Being Locked 196623-379112 205 TX 205-21296 ALEC_USER akaplan 1\ALEC-KAPLAN sqlplus.exe SQL*Plus
Being Locked 196623-379112 205 TX 205-21296 ALEC_USER akaplan 1\ALEC-KAPLAN sqlplus.exe SQL*Plus

OWNER OBJECT_NAME OBJECT_TYPE PROCESS SPID TX_START_DATE TX_STATUS START_SCN 
--------- ------------- ---------- --------- ----- ------------------- --------- ------------
ALEC_USER ADDRESS TABLE 2176:3308 20300 06/10/2014 08:10:43 ACTIVE 230682831978
ALEC_USER MLOG$_ADDRESS TABLE 2176:3308 20300 06/10/2014 08:10:43 ACTIVE 230682831978

==========================
V$LOCK Table in details
==========================
V$LOCK.block
 0 - The session is not blocking other sessions
1 - The session is blocking other sessions

V$LOCK.request
0 - The session is not requesting for a lock
1-6 - The session is requesting for a lock. The number gives additional info.

V$LOCK.lmode  
0 - The session is not holding a lock
1-6 - The sessions is holding a lock. The number gives additional info.

V$LOCK.id1 and V$LOCK.id2
When sessions A is locking sessions B - sessions could be related by valID1 and ID2  values.

V$LOCK.sid
Session Identifier

V$LOCK.type
User Type Lock or System Type Lock
- User type locks:
  TM - DML enqueue
  TX - Transaction enqueue
  UL - User supplied

- System Types Locks
  Are held for extremely short periods of time. They are listed below.

  BL - Buffer hash table instance
  NA..NZ - Library cache pin instance (A..Z = namespace)
  CF - Control file schema global enqueue
  PF - Password File
  CI - Cross-instance function invocation instance
  PI, PS - Parallel operation
  CU - Cursor bind
  PR - Process startup
  DF - datafile instance
  QA..QZ - Row cache instance (A..Z = cache)
  DL - Direct loader parallel index create
  RT - Redo thread global enqueue
  DM - Mount/startup db primary/secondary instance
  SC - System change number instance
  DR - Distributed recovery process
  SM - SMON
  DX - Distributed transaction entry
  SN - Sequence number instance
  FS - File set
  SQ - Sequence number enqueue
  HW - Space management operations on a specific segment
  SS - Sort segment
  IN - Instance number
  ST - Space transaction enqueue
  IR - Instance recovery serialization global enqueue
  SV - Sequence number value
  IS - Instance state
  TA - Generic enqueue
  IV - Library cache invalidation instance
  TS - Temporary segment enqueue (ID2=0)
  JQ - Job queue
  TS - New block allocation enqueue (ID2=1)
  KK - Thread kick
  TT - Temporary table enqueue
  LA .. LP - Library cache lock instance lock (A..P = namespace)
  UN - User name
  MM - Mount definition global enqueue
  US - Undo segment DDL
  MR - Media recovery

LMODE NUMBER Lock mode in which the session holds the lock:
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)

REQUEST
REQUEST NUMBER Lock mode in which the process requests the lock:
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)


==========
Appendix
==========
http://www.orafaq.com/node/854
http://select-star-from.blogspot.co.il/2013/07/how-to-find-sqlsqlid-history-on-oracle.html

bash script by example. Create crontab task, to run a script that does cleanup of old oracle files.

============================
Elaborate Version
============================
============================
General

============================

Common task, of running a crontab task once a day, at 5:00 AM, to delete oracle generated files, that exceed the keep time parameters.

Below are two versions, doing same stuff.

============================
Simple Version
============================
>% crontab -l

0 6 * * * /software/oracle/oracle/scripts/delete_old_trace_files.sh

delete_old_trace_files.sh

export ORA_INST=igt

export DAYS_TO_KEEP=14

export DAYS_TO_KEEP_LIST=7

export LIST_SERVER=tha-ais-2-dwh-1

export LISTENER_ROOT=/software/oracle/diag/tnslsnr/${LIST_SERVER}/lsnr_igt/trace

export LISTENER_ROOT_ALERT=/software/oracle/diag/tnslsnr/${LIST_SERVER}/lsnr_igt/alert

find /software/oracle/diag/rdbms/${ORA_INST}/${ORA_INST}/trace -type f -name "*.trc" -mtime +${DAYS_TO_KEEP} -exec rm {} \;

find /software/oracle/diag/rdbms/${ORA_INST}/${ORA_INST}/trace -type f -name "*.trm" -mtime +${DAYS_TO_KEEP} -exec rm {} \;

find /software/oracle/diag/rdbms/${ORA_INST}/${ORA_INST}/trace -type f -size +1000M -exec rm {} \;

find $LISTENER_ROOT_ALERT  -type f -mtime +${DAYS_TO_KEEP_LIST} -exec rm {} \;

mv -f  ${LISTENER_ROOT}/lsnr_igt.log    ${LISTENER_ROOT}/lsnr_igt.log_1

mv -f  ${LISTENER_ROOT}/lsnr_igt.log_1  ${LISTENER_ROOT}/lsnr_igt.log_2

mv -f  ${LISTENER_ROOT}/lsnr_igt.log_2  ${LISTENER_ROOT}/lsnr_igt.log_3

============================
Elaborate Version
============================

crontab task
>% crontab -l
0 5 * * * /usr/local/etc/homedir/oracle_purge/oracle_purge.sh inst

script
====================================
oracle_purge.sh
====================================

#!/bin/bash
#
# NAME:
#       oracle_purge.sh
#
# ABSTRACT:
#
#       The script cleanup unwanted oracle files
#
# ARGUMENTS:
#
#       Syntax: oracle_purge.sh instance
#
#       instance - the name of the oracle instance we want to cleanup
#       days_to_keep - the number of days to keep trace/log/audit files
#
#
# ENVIRONMENT VAR:
#
#       ORASH
#       BAS_ORACLE_LIST
#
# INPUT FORMAT:
#
# OUTPUT FORMAT:
#
# RETURN/EXIT STATUS:
#       0 - Normal exit
#       1 - Syntax error
#       2 - Unknown instance
#
# LIMITATIONS:
#
# MODIFICATION HISTORY:
# +-------------+------+------------------------------------------------------+
# | Date        | Name |  Description                                         |
# +-------------+------+------------------------------------------------------+
# |             |      |                                                      |
# +-------------+------+------------------------------------------------------+

. /etc/profile > /dev/null 2> /dev/null

FACILITY=`basename $0 .sh`\:
TIMESTAMP=`date +%d_%m_%y_%H_%M`
UNAME=`uname | cut -d_ -f1`
INI_FILE=`dirname $0`/conf/`basename $0 .sh`.ini

if [ $# -ne 1 ] ; then
   echo Syntax: oracle_purge.sh instance>&2
   exit 1
fi

DAYS_TO_KEEP=`grep ^DAYS_TO_KEEP ${INI_FILE} | cut -d= -f2`
MIN_TO_KEEP=$((${DAYS_TO_KEEP}*24*60))

INSTANCE=$1
shift $#

echo $BAS_ORACLE_LIST | grep -q $INSTANCE

if [ $? -ne 0 ] ; then
   echo $FACILITY `date` ERR- $INSTANCE - unknown oracle instance . >&2
   exit 2
fi

# Inits the Oracle environment of the specific instance.
. $ORASH/oracle_login.sh $INSTANCE

# Delete old aud files
if [ $UNAME = 'CYGWIN' ] ; then
  CYG_ORACLE_BASE=`echo $CYG_ORACLE_HOME | cut -d/ -f1-5`
  AUD_FILE_LOC=${CYG_ORACLE_BASE}/admin/${INSTANCE}/adump
else
  AUD_FILE_LOC=/software/oracle/admin/${INSTANCE}/adump
fi

find ${AUD_FILE_LOC} -name '*.aud' -mtime +${DAYS_TO_KEEP} -exec \rm -f {} \;

# Purge Oracle diagnostic files
for home_name in `adrci exec="show homes" | tail -n +2`
do
  if [ $UNAME = 'CYGWIN' ] ; then
    home_name=$(echo $home_name | sed 's/\\/\\\\\\/g')
  fi
  # For each ADR home, purge the files older than 60 days (86400 minutes)
  adrci exec="set home ${home_name} ; purge -age ${MIN_TO_KEEP}"
done

Helper shell script to set environment variables.
====================================
$ORASH/oracle_login.sh
====================================
#!/bin/bash
#
# NAME:
#      oracle_login.sh
#
# ABSTRACT: Defining oracle's environment variables. - Unix version
#
# ARGUMENTS: [p1] - oracle sid
#             Note - 1) the parameter can be omitted
#
#
# SPECIAL FILE:
#
# ENVIRONMENT VAR:
#     PROJECT_SID -
#        should be defined in the following way:
#        globally       - the node run only one oracle instance
#        group login    - there is more than one instance on the current node
#        as a parameter - there is more than one instance per project,
#                         or for system users.
#
# SPECIAL CCC MACROS:
#
# INPUT FORMAT:
#
# OUTPUT FORMAT:
#
# RETURN/EXIT STATUS:
#            1) PROJECT_SID is not defined
#            2) the database PROJECT_SID does not exist
#            3) PROJECT_SID missing from $ORASH/sid.hosts
#            4) ORA_VER missing from $ORASH/sid.hosts
#            5) charcter missing for current sid from $ORASH/charset.dat

if [ "$ORASH" = "" ]; then
   ORASH=$SH_ETC/etc/orash
fi

FACILITY="oracle_login:"
UNAME=`uname | cut -d_ -f1`
UNAME_M=`uname -m`

  if [ "$#" -ge 1 ]; then
      PROJECT_SID=$1
   else
      project_sid_def=`set | grep "^PROJECT_SID=" |wc -l`
      if [ "$project_sid_def" -eq 0 ]; then
         echo "<oracle_login> ERROR>> PROJECT_SID is not defined"
         return 1
      fi
   fi

   ORACLE_BASE=/software/oracle
   project_sid_def=`set | grep "^PROJECT_SID=" |wc -l`
   if [ "$project_sid_def" -eq 0 ]; then
         echo "<oracle_login> ERROR>> PROJECT_SID is not defined"
         return 1
   fi

   ORACLE_HOME=`grep ^"$PROJECT_SID": /var/opt/oracle/oratab | awk '{if (NR==1) print $0}' | cut -d: -f2`
   if [ "$ORACLE_HOME" = "" ]; then
      echo "<oracle_login> ERROR>> the database $PROJECT_SID does not exist in /var/opt/oracle/oratab or /etc/oratab"
      return 2
   fi

   ORA_VER=`grep ^"$PROJECT_SID": $ORASH/sid.hosts | awk '{if (NR==1) print $0}' |cut -d: -f3`
   if [ "$ORA_VER" = "" ]; then
      echo "$FACILITY unable to verify ORA_VER for $PROJECT_SID using $ORASH/sid.hosts"
      return 4
   fi

   DEFAULT_SID=$PROJECT_SID

   ORACLE_SID=$DEFAULT_SID
   TNS_ADMIN=${ORACLE_HOME}/network/admin

#----------------------------------------------------------------#
# the hostname of the DEFAULT_SID appears first in file sid.hosts
#----------------------------------------------------------------#
   HOST=`grep ^"$PROJECT_SID": $ORASH/sid.hosts | awk '{if (NR==1) print $0}' | cut -d: -f2`
   if [ "$HOST" = "" ]; then
      echo "<oracle_login> ERROR>> the database $PROJECT_SID does not exist in $ORASH/sid.hosts"
      return 3
   fi

#-------------------------------------#
#  TWO_TASK is used for a unix clients
#-------------------------------------#
# Disable TWO_TASK feature eliminating the need to change hostname, after disk replication
#   NODE=`hostname`
#   if [ "$NODE" != "$HOST" ]; then
#      TWO_TASK=${DEFAULT_SID}_remote
#   fi

#-------------------------------------#
# setting up NLS_LANG
#-------------------------------------#
   CHARSET=`grep ^"$PROJECT_SID": $ORASH/charset.dat | awk '{if (NR==1) print $0}' | cut -d: -f2`
   if [ "$CHARSET" = "" ]; then
      echo "<oracle_login> ERROR>> the database $PROJECT_SID does not exist in $
ORASH/charset.dat"
      return 5
   fi

   NLS_DATE_FORMAT="DD-MON-RR"
   ORACLE_LPPROG=lpr
   ORACLE_LPARGS="-P la424"
   NLS_LANG="AMERICAN_AMERICA."${CHARSET}
   ORA_NLS32=$ORACLE_HOME/ocommon/nls/admin/data # for oracle
   ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data # for oracle8

   cleaned_path=$PATH
   for ora_path in `grep -v "^#" /var/opt/oracle/oratab | cut -d: -f2 | uniq` ; do
      cleaned_path=`echo -e "${cleaned_path}\c" | tr ':' '\n' | grep -v "$ora_path" | tr '\n' ':'`
   done
   if [ $UNAME = 'SunOS' ] ; then
     PATH=$cleaned_path:$ORACLE_HOME/bin:/usr/ccs/bin:/usr/openwin/bin
   else
     PATH=$cleaned_path$ORACLE_HOME/bin
   fi

   shlib_def=`set | grep "^LD_LIBRARY_PATH" |wc -l`
   if [ "$shlib_def" -eq 0 ]; then
      LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
   else
      cleaned_shlib_path=$LD_LIBRARY_PATH
      for ora_path in `grep -v "^#" /var/opt/oracle/oratab | cut -d: -f2 | uniq` ; do
         cleaned_shlib_path=`echo -e "${cleaned_shlib_path}\c" | tr ':' '\n' | grep -v "$ora_path" | tr '\n' ':'`
      done

      if [ $UNAME = 'SunOS' ] ; then
         LD_LIBRARY_PATH=$cleaned_shlib_path:$ORACLE_HOME/lib:/usr/lib
      else
         LD_LIBRARY_PATH=$cleaned_shlib_path$ORACLE_HOME/lib:/usr/lib
      fi
   fi

   if [ $UNAME_M = 'x86_64' ]; then
      LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:${ORACLE_HOME}/lib32
   fi

   export PROJECT_SID
   export ORACLE_TERM
   export ORACLE_HOME
   export ORACLE_BASE
   export ORACLE_SID
   export TNS_ADMIN
   export ORACLE_SERVER
   export PATH
   export LD_LIBRARY_PATH
   export NLS_LANG
   export ORA_NLS33
   unset ORA_NLS32
   ORACLE_ENV_DEFINED="yes"
   export ORACLE_ENV_DEFINED
   export ORA_VER