When I try to optimize any batch, I get this error:
ORA-01423: error encountered while checking for extra rows in exact fetch
ORA-00942: table or view does not exist
ORA-06512: at line 57
This is the query I am tuning:
SELECT REGEXP_REPLACE (ftvorgn_title, ‘,’, NULL)
FROM ftvorgn x
WHERE ftvorgn_orgn_code = ‘162232’ – v_department
AND ftvorgn_eff_date =
(SELECT MAX (ftvorgn_eff_date)
FROM ftvorgn
WHERE ftvorgn_orgn_code = x.ftvorgn_orgn_code
AND ftvorgn_coas_code = x.ftvorgn_coas_code
AND ftvorgn_eff_date <= SYSDATE);
Am using Advanced SQL Optimization, Optimize SQL by SQL Rewrite options.
From the error message “ORA-00942: table or view does not exist”, it seems that your current schema may not be the correct one which contains your table ftvorgn. Please check the connection and current schema shown at the top-right corner of the Rewrite session to make sure you have the correct connection and schema used.
If this doesn’t help, please contact our Support so they can take a closer look at your environment.
Subject: RE: [SQL Optimizer for Oracle - Discussion Forum] Sql Optimizer 9.2.0 ORA-01423: error encountered while checking for extra rows in exact fetch
From the error message “ORA-00942: table or view does not exist”, it seems that your current schema may not be the correct one which contains your table ftvorgn. Please check the connection and current schema shown at the top-right corner of the Rewrite session to make sure you have the correct connection and schema used.
If this doesn’t help, please contact our Support so they can take a closer look at your environment.
Subject: RE: [SQL Optimizer for Oracle - Discussion Forum] Sql Optimizer 9.2.0 ORA-01423: error encountered while checking for extra rows in exact fetch
From the error message “ORA-00942: table or view does not exist”, it seems that your current schema may not be the correct one which contains your table ftvorgn. Please check the connection and current schema shown at the top-right corner of the Rewrite session to make sure you have the correct connection and schema used.
If this doesn’t help, please contact our Support so they can take a closer look at your environment.
Subject: RE: [SQL Optimizer for Oracle - Discussion Forum] Sql Optimizer 9.2.0 ORA-01423: error encountered while checking for extra rows in exact fetch
From the error message “ORA-00942: table or view does not exist”, it seems that your current schema may not be the correct one which contains your table ftvorgn. Please check the connection and current schema shown at the top-right corner of the Rewrite session to make sure you have the correct connection and schema used.
If this doesn’t help, please contact our Support so they can take a closer look at your environment.
Subject: RE: [SQL Optimizer for Oracle - Discussion Forum] Sql Optimizer 9.2.0 ORA-01423: error encountered while checking for extra rows in exact fetch
From the error message “ORA-00942: table or view does not exist”, it seems that your current schema may not be the correct one which contains your table ftvorgn. Please check the connection and current schema shown at the top-right corner of the Rewrite session to make sure you have the correct connection and schema used.
If this doesn’t help, please contact our Support so they can take a closer look at your environment.
From the screenshot, it seems that you can generate alternatives for your SQL but encountered error when executing. So the Oracle error message may not directly related to the tables in your SQL but something run internally in the product. To help locate the problem, please try use SQL Monitor to monitor SQL executing behind SQL Optimizer while repeating the problem. Then save the monitored SQL and send to Support to ask for help. Support would be able to give you a closer look then.
In a case that this table is existing have one possible solution.
If you are dealing with partition table and during the execution someone drop one of non queried partition, then error is exactly like that (missing partition is in this case something like table).
Tipical proove of this problem is select count(*) from part_table, where during count oracl euses plan which in one moment is not possible…partition is driopped…or PK index is invalid (and is used in plan)