不知道大家有没有在测试12C的时候遇到过AUTOTRACE不能直接使用的情况，我今天就遇到了，在我的PDB里面我想通过AUTOTRACE去追踪一个SQL的执行计划，结果提示我AUTOTRACE没有安装，之前的版本大多都是自动配置好AUTOTRACE信息的，12C为何没有默认安装呢？难道有新功能替代了AUTOTRACE？

SQL > set autotrace on
SP2-0618: Cannot find the Session Identifier.
Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

解决这个问题很简单，只需要运行两个脚本，初始化一下AUTOTRACE就好了。运行$ORACLE_HOME/rdbms/admin/utlxplan.sql脚本初始化AUTOTRACE。

SQL> @?/rdbms/admin/utlxplan

Table created.

该脚本创建了PLAN_TABLE表。还需要运行$ORACLE_HOME/sqlplus/admin/plustrce.sql脚本。

SQL> @?/sqlplus/admin/plustrce
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 select on v_$sesstat to plustrace
                *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> grant select on v_$statname to plustrace;
grant select on v_$statname to plustrace
                *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> grant select on v_$mystat to plustrace;
grant select on v_$mystat to plustrace
                *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL> 
SQL> set echo off

该脚本创建plustrce角色，并授权给DBA角色。这时拥有DBA角色的用户就可以使用AUTOTRACE了。

SQL> set autotrace on
SQL> SELECT * FROM top_test order by id fetch first 5 rows only;

        ID USERNAME
---------- ------------------------------------------------------------
         1 C##DBDREAM
         2 STREAM
         3 PDBADMIN
         4 AUDSYS
         5 GSMUSER

Execution Plan
----------------------------------------------------------
Plan hash value: 763208110

-------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |    40 |  2240 |     3   (0)| 00:00:01 |
|*  1 |  VIEW                    |          |    40 |  2240 |     3   (0)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|          |    40 |  1200 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | TOP_TEST |    40 |  1200 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

本文固定链接: http://www.dbdream.com.cn/2014/01/%e4%bd%bf%e7%94%a8autotrace%e6%98%af%e9%81%87%e5%88%b0sp2-0618%e5%92%8csp2-0611%e9%94%99%e8%af%af/ | 信春哥，系统稳，闭眼上线不回滚！