Tip :- How do I enable AUTOTRACE ?
 
 
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.
 
 
ora816 SamSQL :> 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
 
 
1.Run plustrce.sql through SYS schema if it did not run before as
 
ON NT :- @$ORACLE_HOME\sqlplus\admin\plustrce.sql
ON UNIX :- @$ORACLE_HOME/sqlplus/admin/plustrce.sql
 
2.Grant PLUSTRACE to <USER WHO WANTS TO RUN AUTOTRACE>
 
 Also PLAN_TABLE must exist in the USER’s Schema ,if user want to do autotrace with explain plan . For creation of plan_table ,UTLXPLAN.sql have to be in user’s schema. 
 
 ON NT :- $ORACLE_HOME\rdbms\admin\utlxplan.sql
 ON UNIX :-$ORACLE_HOME/rdbms/admin/utlxplan.sql
 
 User can use the AUTOTRACE options ad follows
 
 
SET AUTOTRACE OFF - No AUTOTRACE report is generated. This is the default. 
SET AUTOTRACE ON EXPLAIN - The AUTOTRACE report shows only the optimizer execution path. 
SET AUTOTRACE ON STATISTICS - The AUTOTRACE report shows only the SQL
 statement execution statistics. 
SET AUTOTRACE ON - The AUTOTRACE report includes both the
 optimizer execution path and the SQL
 statement execution statistics. 

SET AUTOTRACE TRACEONLY - Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any.

End of Tip	Welcome to send comments or feedback at Wadhwa_s@Hotmail.com
Sameer Wadhwa	Copyright © Oracle Techniques All Rights Reserved