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（苦练七十二变，笑对八十一难）
