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

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips