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

image

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;

image

Run plustrace sql

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

image

Grant permission

grant connect,resource,PLUSTRACE to PO;

image

Login as user PO

image

Reference
http://docs.oracle.com/cd/B10501_01/server.920/a96533/autotrac.htm

0 comments:

Post a Comment

 

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