SP2-0618: Cannot find the Session Identifier.Check PLUSTRACE role is enabled

1、今天在scott用户下执行语句跟踪时报了如下错误：

SCOTT@seiang11g>set autotrace traceonly statistice

SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled

SP2-0611: Error enabling STATISTICS report

2、通过上述报错信息分析，是由于scott用户没有PLUSTRACE角色，所以使用SYS用户授予scott用户PLUSTRACE角色的权限：

SYS@seiang11g>grant PLUSTRACE to scott;

grant PLUSTRACE to scott

*

ERROR at line 1:

ORA-01919: role 'PLUSTRACE' does not exist

但是报错信息提示：PLUSTRACE角色不存在，这是因为PLUSTRACE角色在数据库创建时并不会自动创建，这个角色需要手动通过运行plustrce.sql脚本来创建；

[oracle@seiang11g ~]$ cd $ORACLE_HOME/sqlplus/admin

[oracle@seiang11g admin]$ ll

total 16

-rw-r--r-- 1 oracle oinstall 466 Jul 13 13:13 glogin.sql

drwxr-xr-x 2 oracle oinstall 81 Jul 13 10:01 help

-rw-r--r-- 1 oracle oinstall 226 Jul 17 2013 libsqlplus.def

-rw-r--r-- 1 oracle oinstall 813 Mar 7 2006 plustrce.sql

-rw-r--r-- 1 oracle oinstall 2118 Feb 16 2003 pupbld.sql

plustrace.sql脚本内容如下所示：

[oracle@seiang11g admin]$ 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用户下执行该脚本：

SYS@seiang11g>@?/sqlplus/admin/plustrce.sql

SYS@seiang11g>

SYS@seiang11g>drop role plustrace;

drop role plustrace

*

ERROR at line 1:

ORA-01919: role 'PLUSTRACE' does not exist

SYS@seiang11g>create role plustrace;

Role created.

SYS@seiang11g>

SYS@seiang11g>grant select on v_$sesstat to plustrace;

Grant succeeded.

SYS@seiang11g>grant select on v_$statname to plustrace;

Grant succeeded.

SYS@seiang11g>grant select on v_$mystat to plustrace;

Grant succeeded.

SYS@seiang11g>grant plustrace to dba with admin option;

Grant succeeded.

SYS@seiang11g>

SYS@seiang11g>set echo off

脚本执行完毕！

3、最后将PLUSTRACE角色授权给scott用户：

SYS@seiang11g>grant PLUSTRACE to scott;

Grant succeeded.

SCOTT@seiang11g>set autotrace traceonly statistics

注意：在将PLUSTRACE角色授权给scott用户后，需要重新连接scott用户才可以开启会话跟踪。

SCOTT@seiang11g>insert into emp1 select * from emp1;

14 rows created.

Statistics

----------------------------------------------------------

15 recursive calls

22 db block gets

33 consistent gets

5 physical reads

1872 redo size

834 bytes sent via SQL*Net to client

791 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

14 rows processed