oracle解锁

//查询被锁的表
select A.sid, b.serial#,
decode(A.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 Invalida-tion',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
'Unknown') LockType,
c.object_name,
b.username,
b.osuser,
decode(a.lmode, 0, 'None',
1, 'Null',
2, 'Row-S',
3, 'Row-X',
4, 'Share',
5, 'S/Row-X',
6, 'Exclusive', 'Unknown') LockMode,
B.MACHINE,D.SPID
from v$lock a,v$session b,all_objects c,V$PROCESS D
where a.sid=b.sid and a.type in ('TM','TX')
and c.object_id=a.id1
AND B.PADDR=D.ADDR

//用于解锁
alter system kill session 'SID,SERIAL#'

//获得引起阻塞得源头语句(先需要获得sid然后查询，如160)
select sql_text from v$sqlarea
where (v$sqlarea.address, v$sqlarea.hash_value) in (
select sql_address, sql_hash_value
from v$session
where sid in(
select sid from v$session a,x$kglpn b
where a.saddr = b.kglpnuse
and b.kglpnmod <> 0
and b.kglpnmod in (select p1raw from v$session_wait where sid=170 and event like 'library%')

)

----以上为引用，下面是自己的方法





查看表是否锁住
select *
from dba_lock a
where a.lock_id1 in
(select t.object_id
from dba_objects t
where t.object_name = 'B_F_GL_BASE_STDC_AMT_ACC_A')

SELECT
s.username,
decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
o.owner,
o.object_name,
o.object_type,
s.sid,
s.serial#,
s.STATUS,
s.terminal,
s.machine,
s.program,
s.osuser,
s.LOGON_TIME,
l.BLOCK
FROM v$session s, v$lock l, dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT NULL
and l.BLOCK=1

--是否是死锁

--查询谁锁定，谁等待
SELECT
lpad(' ', decode(l.xidusn, 0, 3, 0)) || l.oracle_username User_name,
o.owner,
o.object_name,
o.object_type,
s.sid,
s.serial#
FROM v$locked_object l, dba_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY o.object_id, xidusn DESC;
果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待
以上的语句可以查询到谁锁了表，而谁在等待。
以上查询结果是一个树状结构，如果有子节点，则表示有等待发生。
如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN
找出谁锁定的记录，kill掉就行了。
-----------------------

Oracle杀死死锁进程

先查看哪些表被锁住了:
1获得sid
select b.owner,
b.object_name,
a.session_id,
a.locked_mode from v$locked_object a,
dba_objects b where b.object_id = a.object_id;
##########
2获得sid,serial
select b.username, b.sid, b.serial#, logon_time
from v$locked_object a, v$session b
where a.session_id = b.sid
order by b.logon_time
3杀
alter system kill session 'sid,serial';
如果有ora-00031错误，则在后面加immediate;alter system kill session '29,5497' immediate；
--杀数据库进程（数据库服务器的机子）
SELECT a.username, c.spid AS os_process_id, c.pid AS oracle_process_id
FROM v$session a, v$process c
WHERE c.addr = a.paddr
and a.sid ='1392'
and a.serial# ='10858';