I was working in 10.2.0 version. When I tried to set autotrace to on. I got SP2-0618 error. And here is a small illustration and solution.
SQL> conn hr/hr
Connected.
SQL> set autotrace on;
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SQL> conn / as sysdba
Connected.
SQL> set autotrace on;
SQL>select * from hr.demo1;
ID
----------
1
2
3
4
5
6
7
8
9
10
11
ID
----------
12
13
----------output truncated----------------------------
Execution Plan
----------------------------------------------------------
Plan hash value: 970289426
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 195K| 21 (5)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEMO1 | 50000 | 195K| 21 (5)| 00:00:01 |
---------------------------------------------------------------------------
Statistics not shown.
SQL> conn / as sysdba
Connected.
SQL> alter user scott identified by tiger account unlock;
User altered.
SQL> conn scott/tiger;
Connected.
SQL> set autotrace on;
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SQL> conn / as sysdba
Connected.
SQL> grant plustrace to public;
grant plustrace to public
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
Execute plustrce.sql from $ORACLE_HOME/sqlplus/admin folder.
SQL> @?/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
SQL> 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.
After the execution of the script,
SQL> grant plustrace to public;
Grant succeeded.
SQL> conn hr/hr
Connected.
SQL> set autotrace on;
No error shown.
From hereon, autotrace facility will output properly.
SQL>conn / as sysdba
Connected.
SQL> set autotrace on;
select * from hr.demo1;
ID
----------
1
2
3
4
5
6
7
8
9
10
11
ID
----------
12
13
----------output truncated----------------------------
Execution Plan
----------------------------------------------------------
Plan hash value: 970289426
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 195K| 21 (5)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEMO1 | 50000 | 195K| 21 (5)| 00:00:01 |
---------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3415 consistent gets
0 physical reads
0 redo size
923122 bytes sent via SQL*Net to client
37048 bytes received via SQL*Net from client
3335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50000 rows processed
Statistics also shown.
I also tested the same thing in 11g release 2 and found that that is also true for 11g.
Thats all.
No comments:
Post a Comment
Please write your comments here.