ORA-942 TKPROF Output does not Show Execution Plan (Doc ID 454976.1)

Last updated on JULY 14, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 10.2.0.3 [Release 10.2]
Information in this document applies to any platform.
This problem can occur on any platform.

Symptoms

After running TKPROF on a trace file, using the EXPLAIN=username/password parameter in the command line, the output generated by TKPROF does not show the execution plan for any of the SQL statements, unless the username specified in the EXPLAIN parameter corresponds to the schema under which the SQL was executed.

This is the error message we get in the TKPROF output instead of the execution plans :

error during execute of EXPLAIN PLAN statement
ORA-00942: table or view does not exist



Test case:

conn / as sysdba
create user u1 identified by u1;
create user u2 identified by u2;
grant connect,resource to u1;
grant connect,resource to u2;
conn u1/u1
create table t5(a number);
alter session set events '10046 trace name context forever, level 12';
select * from t5;
alter session set events '10046 trace name context off';

 

 

 

 

Run tkprof with explain parameter as schema owner under which sql statement executed:

$ tkprof ora10gr2_ora_5074.trc ora10gr2_ora_5074.out explain=u1/u1 sys=no     

This Works fine. There is a execution plan along with the row source plan

Run tkprof with explain parameter as username other than u1.

$ tkprof ora10gr2_ora_5074.trc ora10gr2_ora_5074.out explain=u2/u2 sys=no 

In the output file we see the following:

error during execute of EXPLAIN PLAN statement
ORA-0942: table or view does not exist        

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms