last post received-location: kslpsr
last process to post me: 5c002940 1 6
last post sent: 0 0 24
last post sent-location: ksasnd
last process posted by me: 5c002940 1 6
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 000000013F3AF610
O/S info: user: SYSTEM, term: R-DATABASE, ospid: 4368
OSD pid info: Windows thread id: 4368, image: ORACLE.EXE (J000) <-- session258进程为JOB进程
<...snipped...>
SO: 000000025C118F48, type: 4, owner: 000000025C041478, flag: INIT/-/-/0x00
(session) sid: 78 trans: 000000025F510CB0, creator: 000000025C041478, flag: (100041) USR/- BSY/-/-/-/-/-
DID: 0001-0102-000FC0ED, short-term DID: 0000-0000-00000000
txn branch: 000000025F5A9FA0
oct: 0, prv: 0, sql: 0000000000000000, psql: 0000000127BAEDF0, user: 59/MES2ND
service name: SYS$USERS
O/S info: user: SYSTEM, term: R-DATABASE, ospid: 4368, machine: R-DATABASE
program: ORACLE.EXE (J000)
waiting for 'SQL*Net message from dblink' blocking sess=0x0000000000000000 seq=1947 wait_time=0 seconds since wait started=133514
driver id=0, #bytes=1, =0
Dumping Session Wait History
for 'SQL*Net message to dblink' count=1 wait_time=1
driver id=0, #bytes=1, =0
SO: 00000001F46C5DC0, type: 53, owner: 000000025C118F48, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=f46c5dc0 handle=27baedf0 mode=N
call pin=0000000000000000 session pin=0000000000000000 hpc=0000 hlc=0000
htl=00000001F46C5E40[0000000134356368,0000000135C257D0] htb=0000000134356368 ssga=0000000134355C60
user=5c118f48 session=5c118f48 count=1 flags=[0000] savepoint=0x51bdbd17
LIBRARY OBJECT HANDLE: handle=27baedf0 mtx=0000000127BAEF20(1) cdp=1
name=
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN -- ì2ékey aê§ êìa￡ JOB 5 ó ′DDò ′ ￡ é oó D ó
DELETE op_keyaccessory_REAL@DB;
INSERT INTO op_keyaccessory_REAL@DB <--dblink DML操作
SELECT * FROM op_keyaccessory_1766
WHERE create_time >to_date('2013/05/01 01:00:00','yyyy/mm/dd HH24:mi:ss'); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
hash=1156c6d87688ddacd7b1919b7fa4bf5f timestamp=05-16-2013 15:57:05
可见session258是在执行一个dblink DML操作的时候由于Target端的故障（故障原因可能为：1、网络故障2、远端OS故障3、远端DB故障4、远端进程故障，这些故障都可能导致本文问题出现）导致异常，所拥有的资源无法释放，造成了相关session被阻塞。
解决方法：
kill进程J000之后，问题解决。
如果问题频繁出现，需要解决导致原因的4个方面：
1.网络是否不稳定，有异常？
2.DB是否重启过或者其他故障？
3.OS是否有异常，如重启等
4.远端进程是否有异常，如被kill等