SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
今天是2013-09-17,在今天学习sql执行计划的是发现如下问题:
问题描述:
在rhys用户下开启sql语句计划报如下错误:
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
问题分析:
由于该rhys账户没有PLUSTRACE角色导致此问题的产生。
解决办法:
1)查看$ORACLE_HOME/sqlplus/admin下的plustrce.sql发现如下内容:
-- Copyright (c) Oracle Corporation 1995, 2002. All Rights Reserved.
--
-- NAME
-- plustrce.sql
--
-- DESCRIPTION
-- Creates a role with access to Dynamic Performance Tables
-- for the SQL*Plus SET AUTOTRACE ... STATISTICS command.
-- After this script has been run, each user requiring access to
-- the AUTOTRACE feature should be granted the PLUSTRACE role by
-- the DBA.
--
-- USAGE
-- sqlplus "sys/knl_test7 as sysdba" @plustrce
--
-- Catalog.sql must have been run before this file is run.
-- This file must be run while connected to a DBA schema.
create role plustrace;
grant select on v_$statname to plustrace;
grant select on v_$mystat to plustrace;
grant plustrace to dba with admin option;
2)执行该sql创建plustrace角色。
SQL> @./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;
SQL> grant select on v_$sesstat to plustrace;
SQL> set echo off
SQL>
SQL> grant plustrace to rhys;
3)登录rhys账户在此开启sql语句分析功能。
SQL> conn rhys/amy
Connected.
SQL> set autotrace on
SQL>
发现该问题得到解决。
~
SQL> r
1 select ename,job,sal,dname from amy_emp,amy_dept where amy_emp.deptno=amy_dept.deptno
2 and not exists
3* (select * from amy_salgrade where amy_emp.sal between losal and hisal)
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2345751609
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 9 (23)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 42 | 9 (23)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 42 | 9 (23)| 00:00:01 |
| 3 | MERGE JOIN ANTI | | 1 | 29 | 8 (25)| 00:00:01 |
| 4 | SORT JOIN | | 14 | 294 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | AMY_EMP | 14 | 294 | 3 (0)| 00:00:01 |
|* 6 | FILTER | | | | | |
|* 7 | SORT JOIN | | 5 | 40 | 4 (25)| 00:00:01 |
| 8 | TABLE ACCESS FULL | AMY_SALGRADE | 5 | 40 | 3 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | REVERSE_INDEX | 1 | | 0 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID| AMY_DEPT | 1 | 13 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("AMY_EMP"."SAL"<="HISAL")
7 - access(INTERNAL_FUNCTION("AMY_EMP"."SAL")>=INTERNAL_FUNCTION("LOSAL"))
filter(INTERNAL_FUNCTION("AMY_EMP"."SAL")>=INTERNAL_FUNCTION("LOSAL"))
9 - access("AMY_EMP"."DEPTNO"="AMY_DEPT"."DEPTNO")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
530 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>