Wanted to give permission of Auto Trace of other User for tuning purposes?
SQL> create role plustrace;
Role created.
SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.
SQL> grant select on v_$statname to plustrace;
Grant succeeded.
SQL> grant select on v_$mystat to plustrace;
Grant succeeded.
SQL> grant plustrace to test with admin option;
Grant succeeded.
SQL> conn test/test
Connected.
SQL> set autotrace traceonly explain
SQL> select * from T1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4000 | 968K| 44 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 4000 | 968K| 44 (0)| 00:00:01 |
--------------------------------------------------------------------------
There is a sql script in "D:\app\rnagi\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql" which we can execute to create this role and grant role to any user who need trace.
Enjoy!
SQL> create role plustrace;
Role created.
SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.
SQL> grant select on v_$statname to plustrace;
Grant succeeded.
SQL> grant select on v_$mystat to plustrace;
Grant succeeded.
SQL> grant plustrace to test with admin option;
Grant succeeded.
SQL> conn test/test
Connected.
SQL> set autotrace traceonly explain
SQL> select * from T1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4000 | 968K| 44 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 4000 | 968K| 44 (0)| 00:00:01 |
--------------------------------------------------------------------------
There is a sql script in "D:\app\rnagi\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql" which we can execute to create this role and grant role to any user who need trace.
Enjoy!
I'm sorry it's not working :(
ReplyDelete