Skip to Main Content
  • Questions
  • SQL Tuning advisor from SQL Developer throws ORA-00942 table or view does not exist error

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, shiva shekar.

Asked: March 02, 2017 - 5:03 pm UTC

Last updated: March 03, 2017 - 11:04 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi Tom,

I tried to execute SQL tuning advisor for an sql id from back end (PUTTY session). It was keep on executing and never completing.

Command: EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'g49yug9c4aar1_tuning');

I created a tuning task with time out 1800 seconds.

Later, I tries SQL tuning from SQL Developer, it shows the below error. Please help me on this. Can't understand where is the issue.

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : staName75838
Tuning Task Owner : APPS
Tuning Task ID : 87701
Workload Type : Single SQL Statement
Execution Count : 1
Current Execution : EXEC_83140
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 03/02/2017 10:40:13
Completed at : 03/02/2017 10:40:14

-------------------------------------------------------------------------------
Schema Name: APPS
SQL ID : 41p3kgu8n82t8
SQL Text : SELECT ROWID FROM GSI_SPECIAL_ORDER_SUMMARY GSOS WHERE NOT
EXISTS (SELECT 1 FROM GSI_SPECIAL_ORDER_V GSOV WHERE
GSOV.PO_LINE_ID = GSOS.PO_LINE_ID)

-------------------------------------------------------------------------------
ERRORS SECTION
-------------------------------------------------------------------------------
- ORA-00942: table or view does not exist

-------------------------------------------------------------------------------

and Chris said...

Oracle can't find one or more of the tables in your query. Probably because you don't have select privileges on them!

Grant select on them to your user and you should be fine.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, March 03, 2017 - 1:46 pm UTC


A reader, March 03, 2017 - 1:50 pm UTC

Thanks Chris!!

the solution

Johan Vankeerberghen, March 27, 2019 - 10:03 am UTC

If the "explain plan" can be build with out this error. Then the problem is probably that the owner of the table is missing.

This alter session will not help:
ALTER SESSION SET CURRENT_SCHEMA=YouTableOwner

The solution:
Add the xxxx. before for table names . xxxx is you table owner.
e.g.
select * from xxxx.table


To find the owner of you table use this following query:

Select owner
from dba_tables
where table_name = 'YOUR_TABLE_NAME'

More to Explore

DBMS_SQLTUNE

More on PL/SQL routine DBMS_SQLTUNE here