Hi,

I have a batch job in which one of the sql will do insert operation reading from a table which has 5+ Million records.

We have a 314M shared_pool and 100M sort_area. I am getting this error on the first sql, however the rest of the insert statements (reading data from even bigger table go thr' well).

I am trying to understand this problem and fix: Can anyone throw some light on this.

Badrinath
3:05:19 SQL> insert into Remap_Product_new
13:05:19 2 select /*+ USE_HASH(b r) */ distinct
13:05:19 3 b.francode,
13:05:19 4 null,
13:05:19 5 b.francode || '-' || r.new_globalpartnum,
13:05:19 6 'GL',
13:05:19 7 '9999',
13:05:19 8 trunc(sysdate),
13:05:19 9 null
13:05:19 10 from
13:05:19 11 bookings b,
13:05:19 12 remap_product_history r
13:05:19 13 where
13:05:19 14 b.regioncode = r.current_region
13:05:20 15 and b.francode = r.current_francode
13:05:20 16 and b.globalpartnum = r.current_francode || '-' || r.current_globalpartnum
13:05:20 17 and trim(b.manfpartnum) = r.current_manfpartnum
13:05:20 18 and r.rejectflag is null
13:05:20 19 and (b.francode, b.francode || '-' || r.new_globalpartnum)
13:05:20 20 not in (select francode, globalpartnum
13:05:20 21 from products
13:05:20 22 where replacedflag is null);
b.regioncode = r.current_region
*
ERROR at line 14:
ORA-04030: out of process memory when trying to allocate 8192 bytes (sort subheap,sort key)


Bookings: 5.5Million rows
remap: 40-50 rows.