Today when doing some research on “FAST DUAL”, I came across following error when I try to set autotrace on:
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SQL>
Anyway I ignored the error and ran my sql.
The result was I could see the execution plan but not statistics as follows.
SQL> select sysdate from dual;
SYSDATE
———
17-DEC-07Execution Plan
———————————————————-
Plan hash value: 1388734953—————————————————————–
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
—————————————————————–
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
—————————————————————–SQL>
I research about it and found out that my user don’t have PLUSTRACE role assign and I just need to assign the role to correct the error.
But when I tried to grant the role, I got another error as follows:
SQL> grant plustrace to scott;
grant plustrace to scott
*
ERROR at line 1:
ORA-01919: role ‘PLUSTRACE’ does not exist
After that I create the PLUSTRACE role by running the plustrce.sql at $ORACLE_HOME/sqlplus/admin as follows:
SQL> @’D:\oracle\product\11.1.0\db_1\sqlplus\admin\plustrce.sql’;
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role ‘PLUSTRACE’ does not existSQL> create role plustrace;
Role created.
SQL>
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 dba with admin option;
Grant succeeded.
SQL>
SQL> set echo off
SQL>
Then granted the PLUSTRACE role.
SQL> grant plustrace to scott;
Grant succeeded.
SQL>
After granting I closed my old sqlplus window and open the new window and login as scott user.
when I ran my sql, the result was as expected that is explain plan with statistics as follows:
SQL> set autotrace on
SQL> select sysdate from dual;SYSDATE
———
17-DEC-07Execution Plan
———————————————————-
Plan hash value: 1388734953—————————————————————–
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
—————————————————————–
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
—————————————————————–Statistics
———————————————————-
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
422 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedSQL>
So had good end to a day!
0 responses so far ↓
There are no comments yet...Kick things off by filling out the form below.
Leave a Comment