11G ORA-07445 [evaopn3()+135]

---数据库版本

11.2.0.4.0

--模式：DG

日志报错：

Thu May 16 10:22:30 2019
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x4] [PC:0x968F76B, evaopn3()+135] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/jrdw/jrdw/trace/jrdw_ora_76445.trc (incident=256546):
ORA-07445: exception encountered: core dump [evaopn3()+135] [SIGSEGV] [ADDR:0x4] [PC:0x968F76B] [Address not mapped to object] []
Incident details in: /u01/app/oracle/diag/rdbms/jrdw/jrdw/incident/incdir_256546/jrdw_ora_76445_i256546.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

---trace 文件

*** 2019-05-16 10:22:31.032
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=54tgtuyu35ggz) -----

CREATE TABLE query.liangs_lyq_fzcs_0516 AS
SELECT
h.us_id,
h.cred_dt,
h.pl_id,
h.pe_cnt,
h.bill_no,
kq
FROM (
SELECT a.us_id,
b.bill_reg,
a.cred_dt,
c.pl_id,
b.pe_cnt,
b.bill_no,
case
when c.pl_id in ('7785','11483') then 'Íⲿ'
when c.pl_id ='3671' and c.mposw_result in ('01','0101') then '°×1'
when c.pl_id ='3671' and c.mposw_result in ('02','0202') then '°×2'
else 'ÆäËü' end as kq,
row_number() OVER (
PARTITION BY a.us_id ORDER BY b.bill_reg DESC
) rn
FROM fin.t_fa_bill b
LEFT JOIN fin.t_rcm_appr a
ON b.cred_no = a.cred_no
LEFT JOIN fin.mid_out c
ON a.rcm_num = c.seq_num
WHERE c.pl_id IN ('3671','11483')
AND b.bill_reg >= '20181001'
AND b.bill_reg <= '20181130'
and b.b_sts in ('1','4')
and a.us_id not in(SELECT
t.us_id
FROM (
SELECT a.us_id,
b.bill_reg,
a.cred_dt,
c.pl_id,
row_number() OVER (
PARTITION BY a.us_id ORDER BY b.bill_reg DESC
) rn
FROM fin.t_fa_bill b
LEFT JOIN fin.t_rcm_appr a
ON b.cred_no = a.cred_no
LEFT JOIN fin.mid_out c
ON a.rcm_num = c.seq_num
WHERE c.pl_id IN ('29197', '30537', '30871', '31157', '31443', '31729', '27049', '27357', '27665', '47359', '47297', '51783')
AND b.bill_reg >= '20190214'
AND b.bill_reg <= '20190331'
and b.b_sts in ('1','4')
) t
WHERE t.rn = 1)) h
WHERE h.rn = 1

---oracle suport 

Bug 19583063 : ORA 7445 [EVAOPN3+135]

 文档 ID 19583063.8 

 REDISCOVERY INFORMATION:

if a SQL with sub-query coalescing runs into ORA-7445 evaopn3 then we might

have encountered the same problem.

WORKAROUND:

disable sub-query coalescing

_optimizer_coalesce_subqueries = false;

 ---合并子查询 触发的BUG

 --让对应人员不要使用合并子查询建表

Bug 19583063 : ORA 7445 [EVAOPN3+135]