我每隔半個小時抽一次數(shù)，發(fā)現(xiàn)：
運行正常時：應該在30s內完成。如果超過兩分鐘就需要處理了。如果沒有新增數(shù)據(jù)在10s完成。
處理過程：使用apps/apps

1. 查詢死鎖對象
   --查詢表死鎖：
   select b.username,b.sid,b.serial#,logon_time,c.object_name,a.session_id,a.locked_mode,
   'alter system kill session '''||b.sid||','||b.serial#||''';',b.status,b.state,b.*
   from v$locked_object a,v$session b,dba_objects c
   where a.session_id = b.sid
   and c.object_id = a.object_id
   and c.owner in ('XSR')
   order by b.logon_time;

--包，等的死鎖：
Select b.logon_time,b.SID,b.SERIAL#,b.status,b.state,
a.owner,a.name,a.type,a.session_id,'alter system kill session ''' || b.sid || ',' || b.serial# || ''';',b.*
From dba_ddl_locks a, v$session b
where a.session_id = b.SID
and a.owner = 'XSR'
and a.name like 'XSR_ADT_%'
--and b.status = 'INACTIVE'
order by b.LOGON_TIME;

2. 殺掉active死鎖
   查詢死鎖，將Active狀態(tài)的進程殺掉，active狀態(tài)的進程用alter system kill session;一般執(zhí)行1分鐘后提示，執(zhí)行不成功。此時再查看死鎖，active狀態(tài)會變成killed狀態(tài)。
   此時：刪除抽數(shù)鎖定記錄
3. 刪除抽數(shù)鎖定記錄
   --鎖定記錄表：
   select * from xsr_adt_etl_lock t for update;
4. 重新抽數(shù)
   重新打開報表生成頁面：點EBS抽數(shù)，正常運行。
   --查詢執(zhí)行的sql
   select sql_text
   from v$sql
   where hash_value in
   (select sql_hash_value
   from v$session
   where sid in (select session_id from v$locked_object));

select sql_text
from v$sql
where hash_value in
(select sql_hash_value
from v$session
where sid in (845));

SELECT T.ID, T.MEMBER
FROM XSR_APP_ACCOUNT_D T
WHERE T.APP_ID = 52156
AND EXISTS (SELECT 1
FROM CUX_GL_CODE_V@xsr_to_erp A
WHERE T.MEMBER = A.PARENT_FLEX_VALUE_LOW
AND A.FLEX_VALUE_SET_NAME = 'CPI_COA_SUBACC'
AND a.id_flex_num = :p_coa_id
AND A.FLEX_VALUE <> 'T'
AND A.FLEX_VALUE <> '0')
ORDER BY T.MEMBER