Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-00942 table or view does not exist (table does exist, rights are fine)

742431Jun 10 2010 — edited Feb 28 2011
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?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 28 2011
Added on Jun 10 2010
1 comment
1,038 views