1. 查看被锁的表 SELECT p.spid, a.serial#, c.object_name, b.session_id, b.oracle_username, b.os_user_name FROM v$process p, v$session a, v$locked_object b, all_objects c WHERE p.addr = a.paddr AND a.process = b.process AND c.object_id = b.object_id
2. 查看是哪个进程锁的 SELECT sid, serial#, username, osuser FROM v$session where osuser = 'tangpj'3. 杀掉这个进程 alter system kill session 'sid,serial#';
SELECT session_id FROM v$locked_object; SELECT sid, serial#, username, osuser FROM v$session WHERE sid = 116;
SELECT p.spid, a.serial#, c.object_name, b.session_id, b.oracle_username,
b.os_user_name
FROM v$process p, v$session a, v$locked_object b, all_objects c
WHERE p.addr = a.paddr AND a.process = b.process
AND c.object_id = b.object_id
2. 查看是哪个进程锁的
SELECT sid, serial#, username, osuser FROM v$session where osuser = 'tangpj'3. 杀掉这个进程 alter system kill session 'sid,serial#';
SELECT session_id FROM v$locked_object; SELECT sid, serial#, username, osuser FROM v$session WHERE sid = 116;
ALTER SYSTEM KILL SESSION 'sid,serial#';
LS.OSUSER OS_USER_NAME,
LS.USERNAME USER_NAME,
DECODE(LS.TYPE,
'RW',
'Row wait enqueue lock',
'TM',
'DML enqueue lock',
'TX',
'Transaction enqueue lock',
'UL',
'User supplied lock') LOCK_TYPE,
O.OBJECT_NAME OBJECT,
DECODE(LS.LMODE,
1,
NULL,
2,
'Row Share',
3,
'Row Exclusive',
4,
'Share',
5,
'Share Row Exclusive',
6,
'Exclusive',
NULL) LOCK_MODE,
O.OWNER,
LS.SID,
LS.SERIAL# SERIAL_NUM,
LS.ID1,
LS.ID2
FROM SYS.DBA_OBJECTS O,
(SELECT S.OSUSER,
S.USERNAME,
L.TYPE,
L.LMODE,
S.SID,
S.SERIAL#,
L.ID1,
L.ID2
FROM V$SESSION S, V$LOCK L
WHERE S.SID = L.SID) LS
WHERE O.OBJECT_ID = LS.ID1
AND O.OWNER <> 'SYS'
ORDER BY O.OWNER, O.OBJECT_NAME这个先查出那个SID 锁住了。
select /*+ PUSH_SUBQ */
command_type,
sql_text,
sharable_mem,
persistent_mem,
runtime_mem,
sorts,
version_count,
loaded_versions,
open_versions,
users_opening,
executions,
users_executing,
loads,
first_load_time,
invalidations,
parse_calls,
disk_reads,
buffer_gets,
rows_processed,
sysdate start_time,
sysdate finish_time,
'>' || address sql_address,
'N' status
from v$sqlarea
where address = (select sql_address from v$session where sid = 71)根据SID 查出锁住的SQL
select sql_address from v$session where sid = 71
查出来的 地址 变成 00 了 ,你知道是怎么回事吗???