我每隔半个小时抽一次数，发现：
运行正常时：应该在30s内完成。如果超过两分钟就需要处理了。如果没有新增数据在10s完成。
处理过程：使用apps/apps
- 查询死锁对象
--查询表死锁：
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;
- 杀掉active死锁
查询死锁，将Active状态的进程杀掉，active状态的进程用alter system kill session;一般执行1分钟后提示，执行不成功。此时再查看死锁，active状态会变成killed状态。
此时：删除抽数锁定记录 - 删除抽数锁定记录
--锁定记录表：
select * from xsr_adt_etl_lock t for update; - 重新抽数
重新打开报表生成页面：点EBS抽数，正常运行。
--查询执行的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
