Oracle 9i 開啟autotrace on 檢視執行計劃

reference:http://blog.chinaunix.net/u/11765/showart_471903.html
===================

1.建立表，透過utlxplan指令碼

SQL> @?/rdbms/admin/utlxplan
Table created.

2. 建立同義詞為了多個使用者可以共享一個plan_table 並授權給public

SQL> create public synonym plan_table for plan_table;
Synonym created.
SQL> grant all on plan_table to public;
Grant succeeded.

3.建立plustrace 角色

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

4、將角色許可權授予public

sql>grant plustrace to public;

完成以上幾步就可以使用autotrace 功能

set autotrace off 預設為不開啟

set autotrace on 檢視執行計劃和查詢資訊

set autotrace traceonly 只顯示執行計劃，但不顯示查詢輸出

[@more@]