Autotrace Setting

SET AUTOTRACE TRACEONLY

Autotrace is a very useful feature that used to trace the cost of a sql query and execution plane oracle used for that query.

Any session can run the autotrace by SET AUTOTRACE ON at SQL*PLUS. But you will get the following error if it is not enabled.

sql :> set autotrace on;
SP2-0613: Unable to verify PLAN_TABLE format or existence
SP2-0611: Error enabling EXPLAIN report
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

To solve this do the followings:

1. Run plustrce.sql. The location of plustrce.sql is:
@$ORACLE_HOME/sqlplus/admin/plustrce.sql
sql> @ .../plustrce.sql

2. Grant PLUSTRACE to the user : Sql> GRANT PLUSTRACE to user_name;

3. Also PLAN_TABLE must exist in the USER’s Schema ,if user want to do autotrace with explain plan . For creation of plan_table , execute UTLXPLAN.sql location of the file is:
ON UNIX :-$ORACLE_HOME/rdbms/admin/utlxplan.sql
sql> @../utlxplan.sql

A User can use the AUTOTRACE options as follows:

sql> SET AUTOTRACE OFF ;
sql> SET AUTOTRACE ON EXPLAIN ;
sql> SET AUTOTRACE ON STATISTICS;
sql> SET AUTOTRACE ON;
sql> SET AUTOTRACE TRACEONLY;
- Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any.