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