#! /bin/sh
#  script to trace locking sessions
#  Please make changes where **....** is given, according to your environment
#
ORACLE_HOME=**/oracle/product/8.1.7**
export ORACLE_HOME
ORACLE_SID=**ORACLE8**
export ORACLE_SID
USERNAME="**scott/tiger**"
PATH=$PATH:$ORACLE_HOME/bin:/usr/bin:/usr/sbin
export PATH
INFODIR="**/user01/lockdir**"
cat $INFODIR/lock.sql  >> $INFODIR/lockedsessions.log
echo " DATE                      : `date`" > $INFODIR/lock.sql
echo " These are the sessions holding the lock more than 10 minutes " >> $INFODIR/lock.sql
echo " ==========================================================" >> $INFODIR/lock.sql
echo " Please verify what they are doing " >> $INFODIR/lock.sql
echo " " >> $INFODIR/lock.sql
echo " " >> $INFODIR/lock.sql
echo " " >> $INFODIR/lock.sql
$ORACLE_HOME/bin/sqlplus  -s  >>  $INFODIR/lock.sql << EOF
$USERNAME
set pagesize 3000
set feedback off
set line 2000
select  distinct s.sid "SID",serial# "SERIAL#",s.username "USER NAME",o.object_name "OBJECT NAME" ,o.owner "OBJECT OWNER" ,
s.status,s.osuser,s.machine,s.terminal,s.program,s.type,s.logon_time ,l.ctime " LOCKED TIME (in Seconds)"
from v\$session s,v\$lock l,v\$locked_object q, dba_objects o where l.sid=s.sid and q.session_id=l.sid and o.object_id=q.object_id and l.ctime > '600';
exit
EOF
echo " " >> $INFODIR/lock.sql
echo " " >> $INFODIR/lock.sql
echo " " >> $INFODIR/lock.sql
>> $INFODIR/lock.sql
/usr/bin/echo " Please make a note about these locks" >>$INFODIR/lock.sql
mailx -s " DB Lock Information " **your_emailid** < $INFODIR/lock.sql