(1)cd [ORACLE_HOME]/rdbms/admin 1 N5 X: K% b7 T. L
(2)作为SYS 登录SQL*Plus;
8 ^1 @. ^& _6 [# ~) Z5 T2 Z (3)运行@utlxplan.sql; + t" f8 H8 B! P2 D: R6 C
(4)运行CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;
5 L$ ?( H. z- M2 u# V (5)运行GRANT ALL ON PLAN_TABLE TO PUBLIC。
, {7 l' b& d! c9 k$ A6 J% h 重申一遍,如果愿意,可以把GRANT 命令中PUBLIC 替换为每个用户。
9 A( v; l* a9 m1 S- |' R 附实例:
1 P, y" ]2 `) b" m& |; C* Q" z 想在scott下使用autotrace : ^# ~# l9 W0 v* z7 D
SQL> set autotrace on; 0 `. U+ W+ P* N' X
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled 9 _) T* J2 H6 b0 M" F6 c. M
SP2-0611: Error enabling STATISTICS report
* Z% e( h0 p, ^2 E p SQL> @/u01/app/oracle/product/10.2.0/db_1/sqlplus/admin/plustrce.sql # n6 `! _& U e, A4 j
SQL>
! F& L6 n7 _+ O: M SQL> drop role plustrace; : A+ L7 x* v: [8 z
Role dropped. 0 ^8 E& {3 B! ?# f$ K9 ]2 [% f% S& Y
SQL> create role plustrace; - L9 g6 n2 {4 W! V- @
Role created.
" P! c/ T: K* q3 j2 f" K; A SQL>
$ F* Q8 @" y- u0 Y. S5 J SQL> grant select on v_$sesstat to plustrace;
6 m R& K) B* O( X. N8 V Grant succeeded.
; T3 V0 C7 R- p% G2 V SQL> grant select on v_$statname to plustrace; - X( V j4 e) R- f/ t
Grant succeeded. 4 J2 `; H P! a# D, ^- r3 x5 a
SQL> grant select on v_$mystat to plustrace;
' G1 h5 R) E9 ^1 V) C) \ Grant succeeded. J; _4 }8 [: k( T
SQL> grant plustrace to dba with admin option;
, @0 C- I2 U; j# C0 r# k; J" q Grant succeeded.
$ Z% ? O# a5 i( ^5 _/ D SQL> ' a3 R+ n# ~5 t- W ?- S( W
SQL> set echo off
& l1 [7 @6 T7 ~. H2 ` SQL>
, h3 P9 F$ L7 `: T8 a SQL>
7 F$ _: f8 I& Q SQL> & J9 c5 e- q3 g7 b6 ?" w
SQL> grant plustrace to scott;
# G) r: j- S8 O; g2 w Grant succeeded.
- r1 t) I: W# O0 Y1 ?8 `- [ 再次设置 ( Y3 M5 Q8 v3 l, }+ X
[oracle@oracle admin]$ sqlplus scott/tiger
5 o$ L" _3 o# u. b6 r! }; h4 F SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 24 14:22:40 2008 $ P Z' w7 S/ ^" w( F
Copyright (c) 1982, 2005, Oracle. All rights reserved.
, d4 Z7 F; f2 ?& ~( G+ t Connected to:
0 |7 o/ W2 |& R! q Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production 9 O" S; ?6 l2 W; D* g& \! D* X
With the Partitioning, OLAP and Data Mining options 6 G# D2 p& J/ w0 q6 M9 ]
SQL> set autotrace on;
' M+ E3 V/ [: f/ {. r SQL> set autotrace traceonly;
3 L" x* ]8 I# i* b' e SQL> set timing on; |