q_lock_info
查看对象锁信息并生成杀锁语句col BLOCKED_OBJECT FOR a30
col BLOCKER_INFO FOR a50
col BLOCKED_INFO FOR a50
col LOCK_SOLVTION FOR a50
SET lines 200
SELECT ob.object_type || ':' || ob.OWNER || '.' || ob.object_name AS blocked_object,
s1.username || ':' || s1.sid || ',' || s1.serial# || ':' || p1.spid || ':' || s1.STATUS || ':' || s1.sql_id AS blocker_info,
s2.username || ':' || s2.sid || ',' || s2.serial# || ':' || p2.spid || ':' || s2.STATUS || ':' || s2.sql_id AS blocked_info,
'ALTER SYSTEM KILL SESSION ''' || s1.sid || ',' || s1.serial# ||''' immediate;' AS lock_solvtion
FROM v$LOCK l1,
v$SESSION s1,
v$LOCK l2,
v$SESSION s2,
v$locked_object lo,
dba_objects ob,
v$process p1,
v$process p2
WHERE s1.sid = l1.sid
AND s2.sid = l2.sid
AND l1.BLOCK = 1
AND l2.request > 0
AND l1.id1 = l2.id1
AND l2.id2 = l2.id2
AND l1.sid = lo.session_id
AND lo.object_id = ob.object_id
AND s1.paddr = p1.addr
AND s2.paddr = p2.addr
/查看对象级别的锁信息
set linesize 1000 pages 100 echo on feedback off
column LOCK_TYPE format A30
column LOCK_HELD format A18
column LOCK_REQUESTED format A11
column STATUS format A13
column LOCK_HELD_SEC format 99999999
column "DB_SID | OS_PID" format A15
column "LOCKED_OBJECT" format A39
column "LOCK HOLDER: DB_USER | OS_USER" format A30
select/*+RULE*/ OWNER||'.'||OBJECT_NAME "LOCKED_OBJECT", ORACLE_USERNAME||' | '||lo.OS_USER_NAME "LOCK HOLDER: DB_USER | OS_USER",l.sid||' | '|| lo.PROCESS "DB_SID,DB_SERIAL# | OS_PID",
decode(TYPE,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table', type)||' | '||
decode(LMODE,
0, 'None',
1, 'Null',
2, 'row share lock',
3, 'row exclusive lock',
4, 'Share',
5, '(SSX)exclusive lock',
6, 'Exclusive', lmode) lock_type,
l.CTIME LOCK_HELD_SEC,
decode(REQUEST,
0, 'None',
1, 'Null',
2, 'row share lock',
3, 'row exclusive lock',
4, 'Share',
5, '(SSX)exclusive lock',
6, 'Exclusive', request) lock_requested,
decode(BLOCK,
0, 'Not Blocking',
1, 'Blocking',
2, 'Global', block) status
from v$locked_object lo, dba_objects do, v$lock l
where lo.OBJECT_ID = do.OBJECT_ID
AND l.SID = lo.SESSION_ID
AND l.BLOCK='1'
order by OWNER,OBJECT_NAME;
prompt
prompt Lock Holder Informations
prompt ===================================
col event format a35
col object_name format a25
select /*+ rule */
d.sid
,decode(d.sql_id,null,d.prev_sql_id,d.sql_id) sql_id
,d.event
,owner||'.'||object_name object_name
,object_type
from dba_objects a,
v$locked_object b,
dba_blockers c,
v$session d
where a.object_id=b.object_id
and b.session_id=c.holding_session
and c.holding_session=d.sid
and d.sid not in (select distinct waiting_session from dba_waiters)
;杀掉会话
SELECT 'alter system kill session ''' || a.sid || ',' || a.serial# || ''' immediate;'
FROM v\$session a,
v\$locked_object b,
dba_objects c,
v\$lock d
WHERE a.sid = b.session_id
AND b.session_id = d.sid
AND b.object_id = c.object_id
AND c.object_name IN ('T', 'T1')
AND d.block = 1
AND d.ctime > 300;plsql杀session
DECLARE
CURSOR c_sess IS
SELECT a.sid, a.serial#
FROM v$session a,
v$locked_object b,
dba_objects c,
v$lock d
WHERE a.sid = b.session_id
AND b.session_id = d.sid
AND b.object_id = c.object_id
AND c.object_name IN ('T', 'T1')
AND d.block = 1
AND d.ctime > 300;
v_sid v$session.sid%TYPE;
v_serial v$session.serial#%TYPE;
sql_stmt VARCHAR2(1000);
BEGIN
OPEN c_sess;
LOOP
FETCH c_sess INTO v_sid, v_serial;
EXIT WHEN c_sess%NOTFOUND;
sql_stmt := 'alter system kill session ''' || v_sid || ',' || v_serial || ''' immediate';
EXECUTE IMMEDIATE sql_stmt;
DBMS_OUTPUT.PUT_LINE(sql_stmt);
DBMS_OUTPUT.PUT_LINE('session:' || v_sid || ',' || v_serial || ' killed');
DBMS_OUTPUT.NEW_LINE;
END LOOP;
CLOSE c_sess;
END;
/
col BLOCKED_OBJECT FOR a30
col BLOCKER_INFO FOR a50
col BLOCKED_INFO FOR a50
col LOCK_SOLVTION FOR a50
SET lines 200
SELECT ob.object_type || ':' || ob.OWNER || '.' || ob.object_name AS blocked_object,
s1.username || ':' || s1.sid || ',' || s1.serial# || ':' || p1.spid || ':' || s1.STATUS || ':' || s1.sql_id AS blocker_info,
s2.username || ':' || s2.sid || ',' || s2.serial# || ':' || p2.spid || ':' || s2.STATUS || ':' || s2.sql_id AS blocked_info,
'ALTER SYSTEM KILL SESSION ''' || s1.sid || ',' || s1.serial# || ''' immediate;' AS lock_solvtion
FROM gv$LOCK l1,
gv$SESSION s1,
gv$LOCK l2,
gv$SESSION s2,
gv$locked_object lo,
dba_objects ob,
gv$process p1,
gv$process p2
WHERE s1.sid = l1.sid
AND s2.sid = l2.sid
AND l1.BLOCK = 1
AND l2.request > 0
AND l1.id1 = l2.id1
AND l2.id2 = l2.id2
AND l1.sid = lo.session_id
AND lo.object_id = ob.object_id
AND s1.paddr = p1.addr
AND s2.paddr = p2.addr
SQL> select * from dba_blockers;
HOLDING_SESSION CON_ID
--------------- ----------
39 0
SQL> select * from dba_waiters;
WAITING_SESSION WAITING_CON_ID HOLDING_SESSION HOLDING_CON_ID LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2
--------------- -------------- --------------- -------------- -------------------------- ---------------------------------------- ---------------------------------------- ---------- ----------
278 0 39 0 Transaction Exclusive Exclusive 589851 1369
SQL> select SESSION_ID,LOCK_TYPE,MODE_HELD,LOCK_ID1,LOCK_ID2 from dba_locks;
SESSION_ID LOCK_TYPE MODE_HELD LOCK_ID1 LOCK_ID2
---------- -------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
11 XR Null 4 0
11 RD Null 1 0
10 Redo Thread Exclusive 1 0
11 Control File Row-S (SS) 0 0
11 RS Row-S (SS) 25 1
246 Media Recovery Share 7 0
246 Media Recovery Share 1 0
246 Media Recovery Share 4 0
246 Media Recovery Share 5 0
246 Media Recovery Share 3 0
246 Media Recovery Share 201 0
SESSION_ID LOCK_TYPE MODE_HELD LOCK_ID1 LOCK_ID2
---------- -------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
18 AE Share 134 0
27 AE Share 134 0
39 AE Share 134 0
56 AE Share 134 0
272 AE Share 134 0
278 AE Share 134 0
284 AE Share 134 0
39 Transaction Exclusive 589851 1369
278 Transaction None 589851 1369
12 Temp Segment Row-X (SX) 3 1
7 KD Exclusive 0 0
SESSION_ID LOCK_TYPE MODE_HELD LOCK_ID1 LOCK_ID2
---------- -------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
246 PW Row-X (SX) 1 0
7 KT Share 19630 0
39 DML Row-X (SX) 81096 0
278 DML Row-X (SX) 81096 0
SELECT
l.session_id,
s.username,
s.sid,
s.serial#,
l.lock_type,
l.mode_held,
l.mode_requested,
ROUND((sysdate - l.ctime) * 24 * 60, 2) AS minutes_locked
FROM
v$lock l
JOIN
v$session s ON l.sid = s.sid
WHERE
(sysdate - l.ctime) * 24 * 60 > 60;
select 'alter system kill session '''||a.sid||','||a.serial#||''' immediate -- '||username||'@'||machine||' ('||program||');'
from v$session a ,v$locked_object b,dba_objects c
where a.sid = b.session_id
and C.OBJECT_ID = b.OBJECT_ID
and C.OBJECT_NAME in ('T','T1')
AND A.status='INACTIVE' and a.last_call_et>1800
and a.OSUSER='oracle'
and a.type='USER'; SELECT s1.sid AS blocking_sid,
s1.serial# AS blocking_serial,
s1.sql_id AS blocking_sql_id,
s1.STATUS AS blocking_status,
s1.last_call_et AS blocking_last_call_et,
s2.sid AS blocked_sid,
s2.serial# AS blocked_serial,
s2.sql_id AS blocked_sql_id,
s2.event AS blocked_event,
s2.STATUS AS blocked_status,
s2.last_call_et AS blocked_last_call_et
FROM v$session s1,
v$session s2
WHERE s1.sid = s2.blocking_session gv$,还需要调整
col BLOCKED_OBJECT FOR a40
col BLOCKER_INFO FOR a50
col BLOCKED_INFO FOR a50
col LOCK_SOLVTION FOR a60
SET lines 220
SELECT s1.inst_id,
ob.object_type || ':' || ob.OWNER || '.' || ob.object_name AS blocked_object,
s1.username || ':' || s1.sid || ',' || s1.serial# || ':' || p1.spid || ':' || s1.STATUS || ':' || s1.sql_id AS blocker_info,
s2.username || ':' || s2.sid || ',' || s2.serial# || ':' || p2.spid || ':' || s2.STATUS || ':' || s2.sql_id AS blocked_info,
'ALTER SYSTEM KILL SESSION ''' || s1.sid || ',' || s1.serial# || ',' || '@' || s1.inst_id ||''' immediate;' AS lock_solvtion
FROM gv$LOCK l1,
gv$SESSION s1,
gv$LOCK l2,
gv$SESSION s2,
gv$locked_object lo,
dba_objects ob,
gv$process p1,
gv$process p2
WHERE s1.sid = l1.sid
AND s2.sid = l2.sid
AND l1.BLOCK = 1
AND l2.request > 0
AND l1.id1 = l2.id1
AND l2.id2 = l2.id2
AND l1.sid = lo.session_id
AND lo.object_id = ob.object_id
AND s1.paddr = p1.addr
AND s2.paddr = p2.addr
order by 1
/ REM srdc_lock_lolders.sql - collect Lock holder information
define SRDCNAME='Lock_Holders'
SET MARKUP HTML ON PREFORMAT ON
set TERMOUT off FEEDBACK off VERIFY off TRIMSPOOL on HEADING off
COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME
select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'||
to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance;
set TERMOUT on MARKUP html preformat on
REM
spool &&SRDCSPOOLNAME..htm
select '+----------------------------------------------------+' from dual
union all
select '| Diagnostic-Name: '||'&&SRDCNAME' from dual
union all
select '| Timestamp: '||
to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual
union all
select '| Machine: '||host_name from v$instance
union all
select '| Version: '||version from v$instance
union all
select '| DBName: '||name from v$database
union all
select '| Instance: '||instance_name from v$instance
union all
select '+----------------------------------------------------+' from dual
/
set linesize 132 pagesize 66 heading on
break on Kill on username on terminal
column Kill heading 'Kill String' format a13
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column lmode heading 'Lock Held' format a20
column request heading 'Lock Requested' format a20
column serial# format 99999
column usernameformat a10heading "Username"
column terminal heading Term format a7
column tab format a35 heading "Table Name"
column owner format a9
column Address format a18
column ctime heading "Held for (s)"
select nvl(S.USERNAME,'Internal') username,
nvl(S.TERMINAL,'None') terminal,
L.SID||','||S.SERIAL# Kill,
U1.NAME||'.'||substr(T1.NAME,1,20) tab,
decode(L.LMODE,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) lmode,
decode(L.REQUEST,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) request,
l.ctime
from V$LOCK L,
V$SESSION S,
SYS.USER$ U1,
SYS.OBJ$ T1
where L.SID = S.SID
and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
and U1.USER# = T1.OWNER#
and S.TYPE != 'BACKGROUND'
order by 1,2,5
/
spool off
set markup html off preformat off
页:
[1]