启用sqlplus的AutoTrace功能

1. SQL文件赋予执行权限

     （1）进入/oracle/product/10.2.1/rdbms/admin目录

     （2）chmod 744 utlxplan.sql

     （3）进入/oracle/product/10.2.1/sqlplus/admin目录

     （4）chmod 744 plustrce.sql

 

      2.  管理员用户登录oracle

 

sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Aug 10 13:40:54 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> conn sys as sysdba
Enter password: 
Connected.

    3. 运行utlxplan并赋权

 

 

SQL> @?/rdbms/admin/utlxplan.sql

Table created.

SQL> grant all on plan_table to cnaps
;

Grant succeeded. 

    可以给特定用户权限也可以授予public。

 

    4. 运行plustrce，运行后执行动作如下

 

 

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 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

 

    5. 授予用户plustrce角色权限

 

SQL> grant plustrace to cnaps
  2  ;

Grant succeeded.

SQL> 

 

   6.使用autotrace功能

 

SQL> conn cnaps/cnaps;
Connected.
SQL> set autotrace on
SQL> set timing on
SQL> 

 

SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告
SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息
SET AUTOTRACE ON ----------------- 包含执行计划和统计信息
SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出