二. 问题分析

$ oerr sp2 618
00618, 0, "Cannot find the Session Identifier. Check PLUSTRACE role is enabledn"
// *Cause: Unable to find the session identifier.
// *Action: Check that the PLUSTRACE role has been granted.

OERR工具提示检查PLUSTRACE角色是否存在,于是做下面的检查
SQL> select role from dba_roles where role like 'PLUS%';
no rows selected

SQL> select * from dba_role_privs where granted_role = 'PLUSTRACE' ;
no rows selected

发现PLUSTRACE 角色不存在,更不存在给找个角色赋了什么权限.

出现SP2-0618错误的根本原因找到了,就是PLUSTRACE角色没有创建,相关trace分析的表的访问权限没有赋予当前用户(aidu)

三. 问题解决

1. 执行$ORACLE_HOME/sqlplus/admin/plustrce.sql ,创建角色,并赋角色权限
$more $ORACLE_HOME/sqlplus/admin/plustrce.sql
-- 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

SQL> conn / as sysdba
Connected.
SQL> @?/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
SQL> create role plustrace;
Role created.
SQL>
SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.
SQL> grant select on v_$statname to plustrace;
Grant succeeded.
SQL> grant select on v_$mystat to plustrace;
Grant succeeded.
SQL> grant plustrace to dba with admin option;
Grant succeeded.
SQL>
SQL> set echo off

2. 执行$ORACLE_HOME/rdbms/admin/utlxplan.sql ,创建plan_table表.

$more $ORACLE_HOME/rdbms/admin/utlxplan.sql
......
create table PLAN_TABLE (
statement_id varchar2(30),
plan_id number,
timestamp date,
remarks varchar2(4000),
operation varchar2(30),
......
time numeric,
qblock_name varchar2(30),
other_xml clob
);

SQL> conn / as sysdba
Connected.
SQL> @?/rdbms/admin/utlxplan.sql

Table created.

3. 创建同义词和赋plan_table访问权限给当前用户(aidu),赋plustrace角色给当前用户

SQL> create or replace public synonym plan_table for system.plan_table;
Synonym created.

SQL> grant select,update,insert,delete on plan_table to aidu;
Grant succeeded.

SQL> grant plustrace to aidu;
Grant succeeded.

4. 当前用户登陆,可以打开autotrace了,OK,问题解决了.
SQL> conn aidu/aidu
Connected.
SQL> set autotrace on;
SQL> select * from dual;

DUM
---
X

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
24 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
407 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed