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

作者：SEian.G（苦练七十二变，笑对八十一难）

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31015730/viewspace-2145193/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31015730/viewspace-2145193/