[ORACLE] DB 表锁定问题

问题出现ORACLE表锁定

SQL> select object_name,session_id,os_user_name,oracle_username,process,locked_mode,status from v$locked_object l, all_objects a where l.object_id=a.object_id;

OBJECT_NAME          SESSION_ID OS_USER_NAME                   ORACLE_USERNAME                PROCESS                            LOCKED_MODE STATUS
-------------------- ---------- ------------------------------ ------------------------------ ---------------------------------- ----------- -------
TRD                      1453 orad4d                         SYS                            45613414                                     2 VALID
TRD                        79 d4dadm                         D4ddb                          46465810                                     3 VALID

锁定表session

SQL> select
(select username||':'||sid||':'||serial# from v$session where sid=a.sid) || ' 阻塞了 ' ||
(select username ||':'||sid||':'||serial# from v$session where sid=b.sid)
from v$lock a,v$lock b
where a.block = 1 and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2;  2    3    4    5

(SELECTUSERNAME||':'||SID||':'||SERIAL#FROMV$SESSIONWHERESID=A.SID)||'阻塞了'||(SELECTUSERNAME||':'||SID||':'||SERIAL#FROMV$SESSIONWHERESID=B.SID)
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
D4ddb :79:21800 阻塞了 SYS:1453:19073

ORACLE 解决办法如下,SAP系统解决办法,取消相应的SAP程 (或取消对应的后台JOB)

select sid,serial# from v$session where osuser='D4ddb'; 
alter system kill session ‘123,3935’;

 

posted on 2020-04-28 20:09  InnoLeo  阅读(165)  评论(0编辑  收藏  举报