Query crash with ORA-32690 -- Bug 6471770. (Doc ID 960690.1)

Last updated on DECEMBER 06, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.3 and later
Information in this document applies to any platform.
This problem can occur on any platform.

Symptoms


On 10.2.0.3 in Production:
When attempting to  run the query below

SQL> INSERT /*+ append */
2 INTO med_clm_sas_&extractId
3 SELECT /*+ use_hash(a,c) */
4 distinct
5 a.claim_pk AS med_clm_id,
6 ims_pat_id,
7 claim_dt AS min_from_dt,
8 bounded_svc_to_dt AS max_to_dt,
9 a.perf_ims_prov_id AS perf_prov_id,
10 source_pay_grp1 AS pay1_grp_cd,
11 source_pay_grp2 AS pay2_grp_cd,
12 source_pay_dtl1 AS pay1_dtl_cd,
13 source_pay_dtl2 AS pay2_dtl_cd,
14 svc_ims_prov_id AS svc_prov_id,
15 svc_prov_zip,
16 payer1_claim_zip AS pay1_clm_zip,
17 payer2_claim_zip AS pay2_clm_zip,
18 lab_chg,
19 charged_amt AS tot_clm_chrg,
20 service AS service_cd,
21 pos, tos, units,
22 line_chg AS charged_amt,
23 diagnosis AS icd9_cd,
24 rank AS claim_med_diag_rnk_nbr,
25 idb,
26 ims_specialty AS perf_prov_spec
27 FROM &&extractId._mx_claim_diag a
28 INNER JOIN provider.provider c on (a.perf_ims_prov_id=c.ims_prov_id)
29 WHERE provider_type = 'I';
old 2: INTO med_clm_sas_&extractId
new 2: INTO med_clm_sas_az_pain0909
old 27: FROM &&extractId._mx_claim_diag a
new 27: FROM az_pain0909_mx_claim_diag a

the following error occurs:

ERROR at line 2:
ORA-32690: Hash Table Infrastructure ran out of memory


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms