ORACLE 10g中的autotrace功能

oracle 10g中，安装好数据库后，set autotrace on命令总是不成功，需要进行一些必要的设置，利用dba角色可以设置，如何使得普通用户能够执行autotrace功能。

1.安装plan_table表

oracle 10g 中默认安装好了plan_table表

>sqlplus “/sysdba”

SQL> @?/rdbms/admin /utlxplan.sql
SQL> create public synonym plan_table for plan_table;
SQL> grant all on plan_table to public;

2.创建plustrace角色

SQL> @?/sqlplus/admin/plustrce.sql
SQL> grant plustrace to public;

SQL> create role plustrace;

角色已创建。

SQL>
SQL> grant select on v_$sesstat to plustrace;

授权成功。

SQL> grant select on v_$statname to plustrace;

授权成功。

SQL> grant select on v_$mystat to plustrace;

授权成功。

SQL> grant plustrace to dba with admin option;

授权成功。

SQL>
SQL> set echo off

将plustrace角色赋给public：
SQL> grant plustrace to public;

3.测试

SQL> set autotrace on
SQL> select * from scott.dept;

 

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |    80 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
        269  recursive calls
          0  db block gets
         54  consistent gets
          0  physical reads
          0  redo size
        783  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          4  rows processed