oracle-scripts/lock_session_sql_info.sh at master · coderdba/oracle-scripts · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
#!/bin/ksh

export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/11.2.0.4.RAC
export ORACLE_SID=RAC29DB21
#
export PATH=/bin:/sbin:/etc:/usr/bin:/usr/sbin:/usr/local/bin:/usr/lib:$ORACLE_HOME/bin:.
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LIBPATH=$ORACLE_HOME/lib
export TNS_ADMIN=/usr/local/tns

scriptpath=$0
script=`basename $scriptpath`
scriptdir=`dirname $scriptpath`
cd $scriptdir
scriptdir=`pwd`
installdir=`dirname $scriptdir`

logdir=${scriptdir}
logfile=${logdir}/${script}.log
tmpfile1=${logdir}/${script}.tmp1
tmpfile2=${logdir}/${script}.tmp2


exec >> $logfile 2>> $logfile

echo
echo =====================================
echo INFO - Locks at `date`


sqlplus -s / as sysdba <<EOF

set pages 1000
set lines 200

prompt
prompt Listing from dba_waiters
prompt ========================
select * from dba_waiters;

prompt
prompt Locked objects by Holding Session
prompt =================================
select *
from   gv\$locked_object a,
       dba_waiters b
where  a.session_id = b.holding_session;


prompt
prompt Locked objects by Waiting Session
prompt =================================
select *
from   gv\$locked_object a,
       dba_waiters b
where  a.session_id = b.waiting_session;


prompt
prompt Session Info - Holding Session
prompt =================================
select *
from   gv\$session a,
       dba_waiters b
where  a.sid = b.holding_session;


prompt
prompt Session Info - Waiting Session
prompt =================================
select *
from   gv\$session a,
       dba_waiters b
where  a.sid = b.waiting_session;


prompt
prompt Holding session
prompt ===================

select b.inst_id, b.sid, b.username, b.machine, b.osuser, b.module, b.sql_id, b.sql_hash_value, c.sql_text
from   dba_waiters a, gv\$session b, gv\$sql c
where  a.holding_session = b.sid
and    b.sql_id = c.sql_id
and    b.sql_hash_value = c.hash_value;
--and    b.sql_child_number = c.child_number;

prompt
prompt Waiting session
prompt ===================

select b.inst_id, b.sid, b.username, b.machine, b.osuser, b.module, b.sql_id, b.sql_hash_value, c.sql_text
from   dba_waiters a, gv\$session b, gv\$sql c
where  a.waiting_session = b.sid
and    b.sql_id = c.sql_id
and    b.sql_hash_value = c.hash_value;
--and    b.sql_child_number = c.child_number;


EOF