关于资产新增接口问题描述
1 问题描述:
2018-01-15 开始,与实物资产系统对接的资产新增接口异常,状态为“处理中”,如下图所示
2 解决方法:
1)查看锁表情况并解锁
select o.object_name table_name,
l.os_user_name osuser,
l.oracle_username "user",
decode(l.locked_mode,
1,
'No Lock',
2,
'Row Share',
3,
'Row Exclusive',
4,
'Share',
5,
'Share Row Excl',
6,
'Exclusive',
null) "Lock Mode",
s.sid,
s.serial#,
s.logon_time,
s.process ap_pid,
p.spid db_pid,
'ALTER SYSTEM KILL SESSION ''' || l.session_id || ', ' || s.serial# ||
''';' killed
from gv$locked_object l, dba_objects o, gv$session s, gv$process p
where l.object_id = o.object_id
and l.session_id = s.sid
-- and o.object_name like 'FA%'
and s.paddr = p.addr;
kill会话,表解锁
2)查看引起所报的事件及相关SQL
select a.event, sa.sql_text, sa.sql_fulltext, a.serial#, a.sid
from gv$session a, gv$sqlarea sa
where sa.address = a.sql_address
and a.sid = 636;
这里发现引起锁表的时间是gc buffer busy acquire,执行的是往FA_DEPRN_DETAIL表插数据
备注:关于gc buffer busy acquire 与 gc buffer busy release
参考:https://blogs.oracle.com/database4cn/rac-gc-buffer-busy-acquire
3)查看相应的表是否被锁
select * from FA.FA_DEPRN_DETAIL for update nowait;
备注:这边执行这个SQL的时候,session一直处于运行中,然后要kill掉执行SQL的会话的时候,plSQL卡了一会,然后发现FA_DEPRN_DETAIL这个表被释放了,所有FA表的锁也解了,接口程序也顺利跑完了,
再重测了一下,问题解决了。