PO订单无法修改

场景：

　　一用户YDL在下PO单的时候突然断电(或死机)，PO单录到一半。重新打开此PO单后，无法修改订单数据。

　　提示：Could not reserve record [2 tries]. Keep trying?

在metalink上搜索，找到如下信息：

POXPOEPO -- Cannot Modify Purchase Order - FRM-40501: ORACLE Error: Unable to Reserve Record for Update or Delete [ID 224088.1]
		修改时间 12-FEB-2007     类型 PROBLEM     状态 PUBLISHED

• fact: Oracle Purchasing
• fact: POXPOEPO - Enter Purchase Orders
• symptom: Cannot modify purchase order
• symptom: Receive errors when opening Purchase Order form
• symptom: Could not reserve record [2 tries]. Keep trying?
• symptom: FRM-40501: ORACLE error: unable to reserve record for update or delete.
• cause: The record in PO_HEADERS_ALL corresponding to the purchase order was locked.

fix: Remove lock on the record in PO_HEADERS_ALL. To remove the lock bounce the database or kill the session that has the table locked. the locks can be reviewed in the table v$lock

---

 

 

 --查找头的object_id
 select * from all_objects t
 where t.object_name = 'PO_HEADERS_ALL'--44937      45084
 
 --查找锁信息
 select  * from v$lock t
 WHERE t.ID1 = 44937
 or t.ID1 = 45084;

 --查找行的object_id
 select * from all_objects t
 where t.object_name = 'PO_LINES_ALL'--44965 45088
 
 --查询行的锁信息
  select  * from v$lock t
 WHERE t.ID1 IN (44965,45088);

 --查询阻塞会话和其他信息
select 'blocker('||lb.sid||':'||sb.username||')-sql:'|| qb.sql_text  blockers,
       'waiter ('||lw.sid||':'||sw.username||')-sql:'|| qw.sql_text  waiters
from  v$lock lb, 
     v$lock lw,
     v$session sb,
     v$session sw,
     v$sql     qb,
     v$sql     qw
    
where lb.sid=sb.sid
and  lw.sid=sw.sid
and  sb.prev_sql_addr=qb.address
and  sw.sql_address=qw.address
and  lb.id1=lw.id1
and  sw.lockwait is not null
and  sb.lockwait is null
and  lb.block=1

	BLOCKERS	WAITERS
1	blocker(276:APPS)-sql:SELECT /* $Header: fdffvs.lc 115.54 2006/01/16 12:57:02 hgeorgi ship $ */ event_code, user_exit FROM fnd_flex_validation_events WHERE flex_value_set_id = :id ORDER BY event_code	waiter (364:APPS)-sql:UPDATE PO_HEADERS_ALL SET APPROVED_FLAG = DECODE(NVL(APPROVED_FLAG,'N'),'N','N','F','F','R'), AUTHORIZATION_STATUS = DECODE(NVL(AUTHORIZATION_STATUS,'INCOMPLETE'), 'INCOMPLETE','INCOMPLETE','REJECTED','REJECTED', 'REQUIRES REAPPROVAL'), CLOSED_CODE = 'OPEN', CLOSED_DATE = NULL, REVISION_NUM = DECODE(:B6 , '', REVISION_NUM, DECODE(NVL(AUTHORIZATION_STATUS,'INCOMPLETE'), 'APPROVED', DECODE(REVISION_NUM, :B6 , REVISION_NUM + 1, REVISION_NUM), REVISION_NUM)), REVISED_DATE = DECODE(:B6 , '', REVISED_DATE, DECODE(NVL(AUTHORIZATION_STATUS,'INCOMPLETE'), 'APPROVED', DECODE(REVISION_NUM, :B6 , :B5 , REVISED_DATE), REVISED_DATE)), LAST_UPDATE_DATE = :B4 , LAST_UPDATED_BY = :B3 , LAST_UPDATE_LOGIN = :B2 WHERE PO_HEADER_ID = :B1
2	blocker(276:APPS)-sql:SELECT /* $Header: fdffvs.lc 115.54 2006/01/16 12:57:02 hgeorgi ship $ */ event_code, user_exit FROM fnd_flex_validation_events WHERE flex_value_set_id = :id ORDER BY event_code	waiter (364:APPS)-sql:UPDATE PO_HEADERS_ALL SET APPROVED_FLAG = DECODE(NVL(APPROVED_FLAG,'N'),'N','N','F','F','R'), AUTHORIZATION_STATUS = DECODE(NVL(AUTHORIZATION_STATUS,'INCOMPLETE'), 'INCOMPLETE','INCOMPLETE','REJECTED','REJECTED', 'REQUIRES REAPPROVAL'), CLOSED_CODE = 'OPEN', CLOSED_DATE = NULL, REVISION_NUM = DECODE(:B6 , '', REVISION_NUM, DECODE(NVL(AUTHORIZATION_STATUS,'INCOMPLETE'), 'APPROVED', DECODE(REVISION_NUM, :B6 , REVISION_NUM + 1, REVISION_NUM), REVISION_NUM)), REVISED_DATE = DECODE(:B6 , '', REVISED_DATE, DECODE(NVL(AUTHORIZATION_STATUS,'INCOMPLETE'), 'APPROVED', DECODE(REVISION_NUM, :B6 , :B5 , REVISED_DATE), REVISED_DATE)), LAST_UPDATE_DATE = :B4 , LAST_UPDATED_BY = :B3 , LAST_UPDATE_LOGIN = :B2 WHERE PO_HEADER_ID = :B1
3	blocker(276:APPS)-sql:SELECT /* $Header: fdffvs.lc 115.54 2006/01/16 12:57:02 hgeorgi ship $ */ event_code, user_exit FROM fnd_flex_validation_events WHERE flex_value_set_id = :id ORDER BY event_code	waiter (364:APPS)-sql:UPDATE PO_HEADERS_ALL SET APPROVED_FLAG = DECODE(NVL(APPROVED_FLAG,'N'),'N','N','F','F','R'), AUTHORIZATION_STATUS = DECODE(NVL(AUTHORIZATION_STATUS,'INCOMPLETE'), 'INCOMPLETE','INCOMPLETE','REJECTED','REJECTED', 'REQUIRES REAPPROVAL'), CLOSED_CODE = 'OPEN', CLOSED_DATE = NULL, REVISION_NUM = DECODE(:B6 , '', REVISION_NUM, DECODE(NVL(AUTHORIZATION_STATUS,'INCOMPLETE'), 'APPROVED', DECODE(REVISION_NUM, :B6 , REVISION_NUM + 1, REVISION_NUM), REVISION_NUM)), REVISED_DATE = DECODE(:B6 , '', REVISED_DATE, DECODE(NVL(AUTHORIZATION_STATUS,'INCOMPLETE'), 'APPROVED', DECODE(REVISION_NUM, :B6 , :B5 , REVISED_DATE), REVISED_DATE)), LAST_UPDATE_DATE = :B4 , LAST_UPDATED_BY = :B3 , LAST_UPDATE_LOGIN = :B2 WHERE PO_HEADER_ID = :B1
4	blocker(276:APPS)-sql:SELECT /* $Header: fdffvs.lc 115.54 2006/01/16 12:57:02 hgeorgi ship $ */ event_code, user_exit FROM fnd_flex_validation_events WHERE flex_value_set_id = :id ORDER BY event_code	waiter (364:APPS)-sql:UPDATE PO_HEADERS_ALL SET APPROVED_FLAG = DECODE(NVL(APPROVED_FLAG,'N'),'N','N','F','F','R'), AUTHORIZATION_STATUS = DECODE(NVL(AUTHORIZATION_STATUS,'INCOMPLETE'), 'INCOMPLETE','INCOMPLETE','REJECTED','REJECTED', 'REQUIRES REAPPROVAL'), CLOSED_CODE = 'OPEN', CLOSED_DATE = NULL, REVISION_NUM = DECODE(:B6 , '', REVISION_NUM, DECODE(NVL(AUTHORIZATION_STATUS,'INCOMPLETE'), 'APPROVED', DECODE(REVISION_NUM, :B6 , REVISION_NUM + 1, REVISION_NUM), REVISION_NUM)), REVISED_DATE = DECODE(:B6 , '', REVISED_DATE, DECODE(NVL(AUTHORIZATION_STATUS,'INCOMPLETE'), 'APPROVED', DECODE(REVISION_NUM, :B6 , :B5 , REVISED_DATE), REVISED_DATE)), LAST_UPDATE_DATE = :B4 , LAST_UPDATED_BY = :B3 , LAST_UPDATE_LOGIN = :B2 WHERE PO_HEADER_ID = :B1
5	blocker(276:APPS)-sql:SELECT /* $Header: fdffvs.lc 115.54 2006/01/16 12:57:02 hgeorgi ship $ */ event_code, user_exit FROM fnd_flex_validation_events WHERE flex_value_set_id = :id ORDER BY event_code	waiter (364:APPS)-sql:UPDATE PO_HEADERS_ALL SET APPROVED_FLAG = DECODE(NVL(APPROVED_FLAG,'N'),'N','N','F','F','R'), AUTHORIZATION_STATUS = DECODE(NVL(AUTHORIZATION_STATUS,'INCOMPLETE'), 'INCOMPLETE','INCOMPLETE','REJECTED','REJECTED', 'REQUIRES REAPPROVAL'), CLOSED_CODE = 'OPEN', CLOSED_DATE = NULL, REVISION_NUM = DECODE(:B6 , '', REVISION_NUM, DECODE(NVL(AUTHORIZATION_STATUS,'INCOMPLETE'), 'APPROVED', DECODE(REVISION_NUM, :B6 , REVISION_NUM + 1, REVISION_NUM), REVISION_NUM)), REVISED_DATE = DECODE(:B6 , '', REVISED_DATE, DECODE(NVL(AUTHORIZATION_STATUS,'INCOMPLETE'), 'APPROVED', DECODE(REVISION_NUM, :B6 , :B5 , REVISED_DATE), REVISED_DATE)), LAST_UPDATE_DATE = :B4 , LAST_UPDATED_BY = :B3 , LAST_UPDATE_LOGIN = :B2 WHERE PO_HEADER_ID = :B1

 --根据上面查询结果查询session表中的信息
 SELECT * FROM v$session where sid in (364,276);
 
 --kill
 alter system kill session '364,63776'; 

---

 

result：最后该PO单可修改。