Can somebody please tell me how to get autotrace working on 8i? I tried to run the sql script found in %ORACLE_HOME%/sqlplus/admin/plustrce.sql but alot of the tables do not exist.

I'm also having a hard time with the explain plan.

Bryan

First check the following:
*PLAN_TABLE table in the user schema ur logging in.
sql > desc PLAN_TABLE - If it exists then u can run the explain plan for the query.
sql > desc PLAN_TABLE - If it doesn't exists then u need to create one.
In order to obtain the script for creating the table u can look in $ORACLE_HOME/rdbms/admin/utlxplan.sql file
or here is the script .....
*************
create table PLAN_TABLE (
statement_id varchar2(30),
timestamp date,
remarks varchar2(80),
operation varchar2(30),
options varchar2(30),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long,
distribution varchar2(30));
*************
* If ur logging in as 'SYSTEM' or 'SYS' user the table will be already available as most of the DBA's activate it as soon the db is created.
* In order to use the Explain Plan u first need to have the PLAN_TABLE then once its there then u need to set the autotrace ON ..in order to activate this feature do this.....
sql > set AUTOTRACE ON;
Now ur all set to run the explain on any query.
In order to de-activate the AUTOTRACE do this...
sql > set AUTOTRACE OFF.
U can also use....additional to that ...
sql > set TIME ON
sql > set TIMINING ON
But remember PLAN_TABLE must EXISTS in the user ur loggin in as or else u get table not found error.
Also u can use OPTIMIZER features like RULE and COST based modes for better performance.

HTH

You solve my problem! I didn't have the table created under the user.

Bryan

Quick Links:
Do you have an Oracle Question?

Best regards,
Oracle Database, SQL, Application, Programming Tips