Toad World® Forums

Sql Optimizer 9.2.0 ORA-01423: error encountered while checking for extra rows in exact fetch

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.

Thank you for your assistance.

Hi,

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.

Thanks,

Alex

Thank you, Alex.

I tried that, and now I get this message:

From: Alex Luk [mailto:bounce-AlexLuk@toadworld.com]

Sent: Tuesday, June 28, 2016 3:34 AM

To: sqloptoracle@toadworld.com

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

RE: Sql Optimizer 9.2.0 ORA-01423: error encountered while checking for extra rows in exact fetch

Reply by Alex Luk

Hi,

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.

Thanks,

Alex

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from SQL Optimizer for Oracle - General Forum
notifications altogether.

SQL Optimizer for Oracle - Discussion Forum

Flag
this post as spam/abuse.

Alex,

Please disregard my last email…I was doing it in TOAD 12.8.

This is what happens when I Optimize from TOAD 12.9:

Any help you can provide is appreciated.

Regards,

Liz

From: Alex Luk [mailto:bounce-AlexLuk@toadworld.com]

Sent: Tuesday, June 28, 2016 3:34 AM

To: sqloptoracle@toadworld.com

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

RE: Sql Optimizer 9.2.0 ORA-01423: error encountered while checking for extra rows in exact fetch

Reply by Alex Luk

Hi,

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.

Thanks,

Alex

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from SQL Optimizer for Oracle - General Forum
notifications altogether.

SQL Optimizer for Oracle - Discussion Forum

Flag
this post as spam/abuse.

Question. How do I contact support?

Thanks.

From: Alex Luk [mailto:bounce-AlexLuk@toadworld.com]

Sent: Tuesday, June 28, 2016 3:34 AM

To: sqloptoracle@toadworld.com

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

RE: Sql Optimizer 9.2.0 ORA-01423: error encountered while checking for extra rows in exact fetch

Reply by Alex Luk

Hi,

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.

Thanks,

Alex

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from SQL Optimizer for Oracle - General Forum
notifications altogether.

SQL Optimizer for Oracle - Discussion Forum

Flag
this post as spam/abuse.

Sorry, to bug you.

This is the version of TOAD I am running. Perhaps the Optimizer does not run with this version?

From: Alex Luk [mailto:bounce-AlexLuk@toadworld.com]

Sent: Tuesday, June 28, 2016 3:34 AM

To: sqloptoracle@toadworld.com

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

RE: Sql Optimizer 9.2.0 ORA-01423: error encountered while checking for extra rows in exact fetch

Reply by Alex Luk

Hi,

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.

Thanks,

Alex

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from SQL Optimizer for Oracle - General Forum
notifications altogether.

SQL Optimizer for Oracle - Discussion Forum

Flag
this post as spam/abuse.

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.

To contact Support, plesae follow this link: https://support.quest.com/create-service-request

Thanks,

Alex

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)

Could you please confirm or deny mine thoughts?