Oracle Error 之SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role

21ewqsxx

使用scott用户登录oracle，想对一sql做autotrace

1 2 3 4 5 6 7 8 9 10 11 12 13

11gracnode1-> sqlplus scott/tiger SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 29 19:16:13 2015 Copyright (c) 1982, 2011, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> set autotrace on SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled SP2-0611: Error enabling STATISTICS report SQL>

根据错误提示，首先想到scott用户需要PLUSTRACE角色权限。

尝试赋予权限

1 2 3 4 5 6 7 8

SQL> conn / as sysdba Connected. SQL> grant plustrace to scott; grant plustrace to scott       * ERROR at line 1: ORA-01919: role 'PLUSTRACE' does not exist SQL>

角色不存在，检查一下Oracle自带的sql脚本。

在$ORACLE_HOME/sqlplus/admin/ 下有如下sql文件

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

11gracnode1-> cat 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. set echo on drop role plustrace; create role plustrace; grant select on v_$sesstat to plustrace; grant select on v_$statname to plustrace; grant select on v_$mystat to plustrace; grant plustrace to dba with admin option; set echo off

可以确定无疑就是这个脚本，用sys用户运行该脚本。

1	11gracnode1-> sqlplus '/as sysdba' @ plustrce.sql

重新赋权于scott用户

1 2	SQL> grant plustrace to scott; Grant succeeded.

问题解决。

1 2 3 4

SQL> conn scott/tiger Connected. SQL> set autotrace on SQL>