Monday, October 22, 2012

0 Set Autotrace

command Result sets Execution Plan Statistics Execution time
set autotrace on YES YES YES NO
set autotrace traceonly NO YES YES NO
set timing on NO NO NO YES

sysdba has permission to run the autotrace even without the PLUSTRACE role.

How to turnoff the trace

set autotrace off

How to grant normal user permission to use autotrace

Normal user needs to have PLUSTRACE role to turn on the trace.

without the PLUSTRACE, normal user would get the error “SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled and SP2-0611: Error enabling STATISTICS report


List role from oracle

Below query list available roles . PLUSTRACE role can be created by install plustrce.sql

SELECT name FROM user$ WHERE type# = 0;


Run plustrace sql

The script normally exists at $ORACLE_HOME/sqlplus/admin/plustrce.sql


Grant permission

grant connect,resource,PLUSTRACE to PO;


Login as user PO




Post a Comment


SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates