5/13/2010

問題記錄_AP_錯誤 APP-SQLAP-10771:無法保留記錄

Symptoms
--------------------------------------------------------------------
錄入付款之商業發票,比對完成後無法保留紀錄情況,無法進行驗證動作。


Cause
--------------------------------------------------------------------
引起原因是記錄(record)被鎖定(lock)了


Solution





--------------------------------------------------------------------
1)運行下例SQL語句找出被鎖定的AP session:




select t3.object_name, -- 对象名称
       t3.object_type, -- 对象类型
       t1.locked_mode 锁模式,
       count(t3.object_name) over(partition by t3.object_name order by t3.object_name range unbounded preceding) 被锁定的会话数r,
       decode(t1.locked_mode,
              0,
              'none',
              1,
              '空',
              2,
              '行共享(RS)-共享表锁',
              3,
              '行专用(RX)-用于行的修改',
              4,
              '共享锁(S)-阻止其他DML操作',
              5,
              '共享行专用(SRX)-阻止其他事务操作',
              6,
              '专用(X)-独立访问使用') 锁类型, --锁模式
       t2.logon_time 会话连接时间,
       round((sysdate - t2.logon_time) * 24 * 60, 2) 连接持续时间,
       t2.sid sid, --SESSION标识,常用于连接其它列
       t2.serial# serial#, --SERIAL#:如果某个SID又被其它的session使用的话则此数值自增加(当一个       SESSION结束,另一个SESSION开始并使用了同一个SID)。
       t2.status, -- Achtive:正执行SQL语句(waiting for/using a resource)   Inactive:等待操作(即等待需要执行的SQL语句    Killed:被标注为删除
       t2.audsid, --审查session ID唯一性,确认它通常也用于当寻找并行查询模式
       t2.username, --当前session在oracle中的用户名。
       t2.process, --客户端进程的ID
       t2.osuser, --客户端操作系统用户名
       t2.terminal, --客户端运行的终端
       t2.machine, --客户端执行的机器
       t2.program, --客户端执行的客户端程序
       t2.module, -- DBMS_APPLICATION_INFO中设置
       t2.action, --DBMS_APPLICATION_INFO中设置
       t2.logon_time,
       t1.oracle_username as ora_username, -- ORACLE系统用户名称
       t1.os_user_name as os_username, -- 操作系统用户名称
       t1.process as process, --进程编号
       'alter system kill session''' || t2.sid || ',' || t2.serial# || '''' kill_session
from   v$locked_object t1,
       v$session       t2,
       all_objects     t3
where  t1.session_id = t2.sid
and    t3.object_id = t1.object_id
and    t3.object_name not like '%$%'
--   and t3.object_name like '%表名%'
order  by 4 desc,
          1;

2)
找出對應的kill session欄位,執行該欄位的alter system kill session 'number',再重新進行驗證動作。


Reference





--------------------------------------------------------------------







    Sign off application, bounce the database and the lock will be released.  Or run the following scripts to identify the locked record and release the lock:
    1. Identify the Oracle serial ID, SID ID and terminate without shutting the database down.
    2. Make sure that the user is logged off.
    3. Type ps -ef |grep
    4. Kill all processes related to that user.
    5. Identify SID, serial#
              select distinct           acc.object, ses.osuser, ses.process,           ses.sid, ses.serial#           from v$access acc,           v$session ses           where (acc.owner != 'SYS'           or acc.object = 'PLAN_TABLE')           and acc.sid = ses.sid           and ses.status != 'INACTIVE'           and ses.type != 'BACKGROUND'           and acc.object not in ('V$ACCESS','V$SESSION')           and ses.audsid != userenv('SESSIONID')           order by 1,2,3           /     6  Double-check the identified SID and serial ID:          SELECT osuser,          username,          process,          sid,          serial#,          status,          to_char(logon_time,'DD-MON HH24:MI:SS') logon_time,          machine,          program          FROM v$session          WHERE sid = &SID_NUM          /        7  ALTER SYSTEM KILL SESSION '&SID_NUM,&SERIAL_NUM';            Alternatively use the following scripts to identify the blocking session:            -- check for locked tables            select a.object_id, a.session_id, substr(b.object_name, 1, 40)            from v$locked_object a,            dba_objects b            where a.object_id = b.object_id            order by b.object_name ;            --find_blocked.sql            select sid,            decode(block ,0,'NO','YES') BLOCKER,            decode(request,0,'NO','YES') WAITER            from v$lock            where request > 0 or block > 0 order by block desc            /            SELECT 'alter system kill session '''||vs.sid||','||vs.serial#||'''' ,al.object_name, al.object_type, vs.status,            fu.user_name,vs.process,vs.osuser,vs.username,            to_char(vs.logon_time,'DD-MON HH24:MI:SS') logon_time, vs.program            FROM fnd_logins fl, fnd_user fu, all_objects al, v$lock vl, v$session vs            WHERE fl.pid = vl.sid            AND vl.id1 = al.object_id (+)            AND fl.user_id = fu.user_id            AND to_char (start_time, 'DD-MON-RR') = to_char (sysdate, 'DD-MON-RR')            and vs.sid=vl.sid            and vl.sid = &sid            'ALTERSYSTEMKILLSESSION'''||VS.SID||','||VS.SERIAL#||''''