oracle并发请求异常,运行时间超长(一般情况下锁表)

1、如果前台无法取消请求出现错误:
oracle并发请求异常,运行时间超长(一般情况下锁表)
则后台更新
update fnd_concurrent_requests
   set status_code = 'X', phase_code = 'C'
 where request_id in ('6779908') ;
2、后台如果更新失败,则检查锁表(1中事务不提交):
    
spool c:\lock.txt
set line 1000
set echo off
set serveroutput on
--set feedback off prompt '获取数据.....'
create table oldnong_session as
select a.username, a.sid, a.serial#,
a.lockwait, a.machine,a.status,
a.last_call_et,a.sql_hash_value,a.program
from v$session a
where nvl(a.username,'NULL')< >'NULL'; create table oldnong_lock as
select id1, kaddr, sid, request,type
from v$lock; /*
create table oldnong_sqltext as
select hash_value , sql_text
from v$sqltext s, oldnong_session m
where s.hash_value=m.sql_hash_value;
*/ column username format a10
column machine format a15
column last_call_et format 99999 heading "Seconds"
column sid format 9999 prompt "正在等待别人的用户"
select a.sid, a.serial#,
a.machine,a.last_call_et, a.username, b.id1
from oldnong_session a, oldnong_lock b
where a.lockwait = b.kaddr
/ prompt "被等待的用户"
select a.sid, a.serial#,
a.machine, a.last_call_et,a.username,
b.type,a.status,b.id1
from oldnong_session a, oldnong_lock b
where b.id1 in
(select distinct e.id1
from oldnong_session d, oldnong_lock e
where d.lockwait = e.kaddr)
and a.sid = b.sid
and b.request=0
/ /*
prompt "查出其sql "
select a.username, a.sid, a.serial#,
b.id1, b.type, c.sql_text
from oldnong_session a, oldnong_lock b, oldnong_sqltext c
where b.id1 in
(select distinct e.id1
from oldnong_session d, oldnong_lock e
where d.lockwait = e.kaddr)
and a.sid = b.sid
and b.request=0
and c.hash_value =a.sql_hash_value
/
*/ prompt "删除临时表"
drop table oldnong_session;
drop table oldnong_lock;
--drop table oldnong_sqltext; spool off

  

3、找出被等待的SID,后台杀掉
alter system kill session 'SID,SERIAL# ';
select b.SID,b.SERIAL#, b.USERNAME,
       b.SCHEMANAME, b.MACHINE,
       b.TERMINAL, b.PROGRAM,
       b.STATUS, b.MODULE,
       b.logon_time,b.action
  from v$session b
WHERE b.SID='';
核实是不是请求提交的时间
4、提交1,问题解决
上一篇:ADF_Advanced ADF系列2_Fusion应用的客制和个性化(Part2)


下一篇:python redis操作