ORA-942 TKPROF Output does not Show Execution Plan
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.
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 :
ORA-00942: table or view does not exist
create user u1 identified by u1;
create user u2 identified by u2;
grant connect,resource to u1;
grant connect,resource to u2;
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:
This Works fine. There is a execution plan along with the row source plan
Run tkprof with explain parameter as username other than u1.
In the output file we see the following:
ORA-0942: table or view does not exist
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