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

相关推荐: oracle的ITL

一、ITL描述： ITL(Interested Transaction List)是Oracle数据块内部的一个组成部分，位于数据块头（block header），itl由xid，uba，flag，lck和scn/fsc组成，用来记录该块所有发生的事务，一个i…