In the documentation for TKPROF it mentions:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/sqltrace.htm#1018
Table 10-2 TKPROF Arguments
Argument Description
EXPLAIN
Determines the execution plan for each SQL statement in the trace file and writes
these execution plans to the output file. TKPROF determines execution plans by
issuing the EXPLAIN PLAN statement after connecting to Oracle with the user and
password specified in this parameter. The specified user must have CREATE
SESSION system privileges. TKPROF takes longer to process a large trace file
if the EXPLAIN option is used.
TABLE
Specifies the schema and name of the table into which TKPROF temporarily
places execution plans before writing them to the output file. If the specified table
already exists, then TKPROF deletes all rows in the table, uses it for the EXPLAIN
PLAN statement (which writes more rows into the table), and then deletes those rows.
If this table does not exist, then TKPROF creates it, uses it, and then
drops it.
The specified user must be able to issue INSERT, SELECT, and DELETE
statements against the table. If the table does not already exist, then the user
must also be able to issue CREATE TABLE and DROP TABLE statements. For
the privileges to issue these statements, see the Oracle9i SQL Reference. =
I have the appropriate privileges
SQL> connect forbesc@dev
Enter password: *******
Connected.
SQL> create table drop_this (f1 number);
Table created.
SQL> drop table drop_this;
Table dropped.
But I am prevented from creating an EXPLAIN PLAN with TKPROF - via the table which gets created and then dropped - using the Syntax:
tkprof dev_s001_22566.trc chuck_tkprof2.out EXPLAIN=forbesc/******
Here's what I get in the TKPROF output
TKPROF: Release 10.1.0.3.0 - Production on Thu Jan 12 13:57:17 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
[oracle@ora4 bdump]$ more chuck_tkprof2.out
TKPROF: Release 10.1.0.3.0 - Production on Thu Jan 12 13:57:17 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Trace file: dev_s001_22566.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
Error in CREATE TABLE of EXPLAIN PLAN table: FORBESC.prof$plan_table
ORA-00922: missing or invalid option
parse error offset: 1052
EXPLAIN PLAN option disabled.
********************************************************************************
...
Could someone point me to what I'm doing incorrectly?
Thanks,
Chuck