set autotrace on時報SP2-0618錯誤的問題解決案例

一. 問題描述

登陸了資料庫後,想開啟trace,檢查sql語句的執行計劃.在執行set autotrace on命令報SP2-0618的錯誤,如下:
SQL>conn aidu/aidu
connected.
SQL>set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report[@more@]

二. 問題分析

$ 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