We are facing a strange problem with the "table or view does not exist" error. To rule out the two common reasons for that, we do have the necessary rights and the tables really exist. Now here's what we do:
MERGE INTO gas_price REAL
USING (SELECT pt.*, sp.ID
FROM poi_gas_price_t pt LEFT JOIN static_poi sp
ON (pt.supplier_id = sp.supplier_id AND pt.supplier_poi_id = sp.supplier_poi_id)
WHERE pt.session_id = ? AND pt.supplier_id = ?) tmp
ON ( tmp.supplier_poi_id = REAL.supplier_poi_id
AND tmp.supplier_id = REAL.supplier_id
AND tmp.grade = REAL.grade)
WHEN MATCHED THEN
UPDATE
SET REAL.station_id = tmp.ID, REAL.price = tmp.price, REAL.last_update = tmp.last_update,
REAL.timestamp_local = tmp.timestamp_local, REAL.timestamp_univ = tmp.timestamp_univ,
REAL.currency = tmp.currency, REAL.unit_of_measure = tmp.unit_of_measure
WHERE tmp.timestamp_local >= REAL.timestamp_local
WHEN NOT MATCHED THEN
INSERT (REAL.station_id, REAL.price, REAL.last_update, REAL.timestamp_local,
REAL.timestamp_univ, REAL.currency, REAL.unit_of_measure, REAL.supplier_poi_id,
REAL.supplier_id, REAL.grade)
VALUES (tmp.ID, tmp.price, SYSDATE, tmp.timestamp_local, tmp.timestamp_univ, tmp.currency,
tmp.unit_of_measure, tmp.supplier_poi_id, tmp.supplier_id, tmp.grade)
LOG ERRORS INTO ERR$_GAS_PRICE REJECT LIMIT UNLIMITED
select count(*) from user_objects where object_name in ('POI_GAS_PRICE_T','STATIC_POI','GAS_PRICE', 'ERR$_GAS_PRICE');
-> 4
POI_GAS_PRICE_T is a global temporary table (on commit delete)
gas_price and static_poi are heap organized tables
ERR$_GAS_PRICE logs errors and is empty
According to our application the merge statement causes the ORA-00942 error. It's running in a 4 node RAC environment
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
We recently moved there from a 2 node RAC environment where this never happened.
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit.
A somewhat vague theory is that this happens due to different memory constraints the two environments impose. Maybe this is related to the new temporary tablespace introduced in 11g? Any idea, things I should investigate in?