/************************ wait.sql *************************/
set pages 2000
set lines 132
column p1text format a18
column event format a35
column WT format 9999
column SW format 9999
select sid, event,p1text,p1raw,p1,p2,p3,seconds_in_Wait “SW”,Wait_time “WT”
from v$session_wait
where event not in(
‘KXFQ: Dequeue Range Keys – Slave’,
‘KXFQ: Dequeuing samples’,
‘KXFQ: kxfqcls – consumer closing TQ’,
‘KXFQ: kxfqdeq – dequeue from specific qref’,
‘KXFQ: kxfqdeq – normal deqeue’,
‘KXFX: Execution Message Dequeue – Slave’,
‘KXFX: Message Fragment Dequeue – Slave’,
‘KXFX: Parse Reply Dequeue – Query Coord’,
‘Null event’,
‘PL/SQL lock timer’,
‘Parallel Query Idle Wait – Slaves’,
‘Replication Dequeue’,
‘SQL*Net message from client’,
‘buffer deadlock’,
‘dispatcher timer’,
‘io done’,
‘pipe get’,
‘pmon timer’,
‘rdbms ipc message’,
‘rdbms ipc message block’,
‘rdbms ipc reply’,
‘slave wait’,
‘smon timer’,
‘virtual circuit status’)
/
/****************** locktree.sql **********************/
rem
rem $Header: utllockt.sql 21-jan-2003.16:21:56 bnnguyen Exp $ locktree.sql
rem
Rem Copyright (c) 1989, 2003, Oracle Corporation. All rights reserved.
Rem NAME
REM UTLLOCKT.SQL
Rem FUNCTION – Print out the lock wait-for graph in tree structured fashion.
Rem This is useful for diagnosing systems that are hung on locks.
Rem NOTES
Rem MODIFIED
Rem bnnguyen 01/21/03 – bug2166717
Rem pgreenwa 04/27/95 – fix column definitions for LOCK_HOLDERS
Rem pgreenwa 04/26/95 – modify lock_holders query to use new dba_locks f
Rem glumpkin 10/20/92 – Renamed from LOCKTREE.SQL
Rem jloaiza 05/24/91 – update for v7
Rem rlim 04/29/91 – change char to varchar2
Rem Loaiza 11/01/89 – Creation
Rem
/* Print out the lock wait-for graph in a tree structured fashion.
*
* This script prints the sessions in the system that are waiting for
* locks, and the locks that they are waiting for. The printout is tree
* structured. If a sessionid is printed immediately below and to the right
* of another session, then it is waiting for that session. The session ids
* printed at the left hand side of the page are the ones that everyone is
* waiting for.
*
* For example, in the following printout session 9 is waiting for
* session 8, 7 is waiting for 9, and 10 is waiting for 9.
*
* WAITING_SESSION TYPE MODE REQUESTED MODE HELD LOCK ID1 LOCK ID2
* —————– —- —————– —————– ——– ——–
* 8 NONE None None 0 0
* 9 TX Share (S) Exclusive (X) 65547 16
* 7 RW Exclusive (X) S/Row-X (SSX) 33554440 2
* 10 RW Exclusive (X) S/Row-X (SSX) 33554440 2
*
* The lock information to the right of the session id describes the lock
* that the session is waiting for (not the lock it is holding).
*
* Note that this is a script and not a set of view definitions because
* connect-by is used in the implementation and therefore a temporary table
* is created and dropped since you cannot do a join in a connect-by.
*
* This script has two small disadvantages. One, a table is created when
* this script is run. To create a table a number of locks must be
* acquired. This might cause the session running the script to get caught
* in the lock problem it is trying to diagnose. Two, if a session waits on
* a lock held by more than one session (share lock) then the wait-for graph
* is no longer a tree and the conenct-by will show the session (and any
* sessions waiting on it) several times.
*/
/* Select all sids waiting for a lock, the lock they are waiting on, and the
* sid of the session that holds the lock.
* UNION
* The sids of all session holding locks that someone is waiting on that
* are not themselves waiting for locks. These are included so that the roots
* of the wait for graph (the sessions holding things up) will be displayed.
*/
drop table lock_holders;
create table LOCK_HOLDERS /* temporary table */
(
waiting_session number,
holding_session number,
lock_type varchar2(26),
mode_held varchar2(14),
mode_requested varchar2(14),
lock_id1 varchar2(22),
lock_id2 varchar2(22)
);
drop table dba_locks_temp;
create table dba_locks_temp as select * from dba_locks;
/* This is essentially a copy of the dba_waiters view but runs faster since
* it caches the result of selecting from dba_locks.
*/
insert into lock_holders
select w.session_id,
h.session_id,
w.lock_type,
h.mode_held,
w.mode_requested,
w.lock_id1,
w.lock_id2
from dba_locks_temp w, dba_locks_temp h
where h.blocking_others = ‘Blocking’
and h.mode_held != ‘None’
and h.mode_held != ‘Null’
and w.mode_requested != ‘None’
and w.lock_type = h.lock_type
and w.lock_id1 = h.lock_id1
and w.lock_id2 = h.lock_id2;
commit;
drop table dba_locks_temp;
insert into lock_holders
select holding_session, null, ‘None’, null, null, null, null
from lock_holders
minus
select waiting_session, null, ‘None’, null, null, null, null
from lock_holders;
commit;
column waiting_session format a17;
column lock_type format a17;
column lock_id1 format a17;
column lock_id2 format a17;
/* Print out the result in a tree structured fashion */
select lpad(‘ ‘,3*(level-1)) || waiting_session waiting_session,
lock_type,
mode_requested,
mode_held,
lock_id1,
lock_id2
from lock_holders
connect by prior waiting_session = holding_session
start with holding_session is null;
drop table lock_holders;
/********************* enq_10_sql.sql *************************/
set lines 132
set pages 2000
col SQ_L format a64 word_wrapped heading “SQL executed by Session waiting on TX Enqueue”
col UN format a10 heading “ORA User”
col OS format a10 heading “OS User”
col MA format a20 heading “Machine”
col SI format 9999 heading “SID”
col SR format 99999 heading “Ser#”
break on SI on SR on UN on OS on MA skip 1
select /*+ORDERED USE_NL(A B)*/ b.sql_Text “SQ_L”,a.sid SI,a.serial# SR,a.username UN,a.osuser OS,
substr(a.machine,1,20) MA
from v$session a,v$SQLTEXT_WITH_NEWLINES b
where a.event = ‘enq: TX – row lock contention’
and a.SQL_ADDRESS=b.ADDRESS
and a.SQL_HASH_VALUE =b.HASH_VALUE
order by a.sid,a.serial#,b.address,b.hash_value,b.piece
/
/************************** lockid.sql **********************/
set lines 132
col object_name format a40
select sid, lockwait,BLOCKING_SESSION,serial#, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#,e.object_name,
ROW_WAIT_ROW#,DBMS_ROWID.ROWID_CREATE(1, e.DATA_OBJECT_ID, d.RELATIVE_FNO, c.ROW_WAIT_BLOCK#, c.ROW_WAIT_ROW# ) locked_rowid
from v$session c,
dba_data_files d,
dba_objects e
where
FILE_ID=c.ROW_WAIT_FILE#
and lockwait is not null
and e.object_id = c.ROW_WAIT_OBJ#
/
/************************ user.sql ***********************/
col osuser format a14
select sid,serial#,SQL_HASH_VALUE,sql_address,PREV_HASH_VALUE,PREV_SQL_ADDR,event,
program,osuser,machine,username,status,to_char(logon_time,’DD-MON-YY HH24:MI:SS’) from v$session where SID = &1
/
/******************** get_spid.sql **************************/
set lines 132 pages 2000
accept val1 number prompt “Enter Oracle SID:”
column spid heading “Unix PID”
column sql_address heading “Address of Current SQL”
column username heading “Oracle Username”
column machine heading “Machine name” format a30
column kill_script heading “Unix Kill script”
select spid ,a.sql_address,a.username,a.machine,’kill -9 ‘||b.SPID kill_script
from v$session a,
v$process b
where a.paddr=b.addr and a.sid = &val1
/