Oracle认证：生成autotrace权限的实例讲解

会计考友

一、配置autotrace
1、cd [ORACLE_HOME]/rdbms/admin;
2、以system用户登陆;
3、执行@utlxplan;
, ^0 C$ ?% D5 n' L8 D+ ?/ p  x/ D4、执行create public synonym plan_table for plan_table;
5、执行 grant all on plan_table to public;
0 g2 r2 s. x# Z/ F$ Y, @: M0 c二、创建plustrace角色
1、cd [ORACLE_HOME]/sqlplus/admin;
2、以sys或sysdba登陆;
. x  t+ @/ I  N3、运行@plustrce;
+ E( Y5 ^# H  s) L- j' S4、执行grant plustrace to public;
% v  Z1 G% t4 O0 n9 j$ J3 c7 E注：也可以将public指定为某个用户。
. X1 }" b) j! S+ D
- @/ I7 b! @/ V. o4 c( {. ^) ^[oracle@olivenan oracle]$ cd $ORACLE_HOME/rdbms/admin/
9 ~4 E* ?  m# J# Z3 p[oracle@olivenan admin]$ sqlplus system/oracle

" o( Q3 n8 @( D5 I& r8 iSQL*Plus: Release 9.2.0.4.0 - Production on Wed Jun 27 11:00:20 2007
4 D2 O* T3 ]4 f% a! }6 m0 O
, Y0 K- W! Q( b0 ]( ^Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

) S6 b* [% z" z4 r
( y5 N& M) s" zConnected to:
( U* W/ t1 {; k7 |2 vOracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> @utlxplan

Table created.
  F& A" Z+ `+ d
' ]' m' r( H! s; U% Y! G; RSQL> create public synonym plan_table for plan_table;

Synonym created.
# x4 S- `7 h; Q/ N; B# \' d) |
& V  w+ `3 S& L. ]( hSQL> grant all on plan_table to public;
2 E1 \/ i  m) V
Grant succeeded.

SQL> exit

会计考友

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
1 U. s4 b5 N" H2 i# M2 g# ^; V9 JWith the Partitioning, OLAP and Oracle Data Mining options
2 h) \+ |0 ]; t" HJServer Release 9.2.0.4.0 - Production
( Z* L# m2 \; I0 m" V" h1 m[oracle@olivenan admin]$ cd $ORACLE_HOME/sqlplus/admin
[oracle@olivenan admin]$ sysplus "/as sysdba"
0 U+ b" Z7 R. Y. Q' J* w-bash: sysplus: command not found
[oracle@olivenan admin]$ sqlplus "/as sysdba"
3 l" X% p3 C2 M
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Jun 27 11:04:55 2007
& ]& a% p' i1 z" u! W- A
! W- y0 k4 Y# |' ~9 v8 xCopyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


$ ?1 ^; M  u2 c9 p+ |: lConnected to:
, F* H3 a8 X8 Q, E. TOracle9i Enterprise Edition Release 9.2.0.4.0 - Production
6 o5 P$ S; y' r" h% s( j) k  zWith the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
# J4 ?  S6 U" m' n, B* G* h
' B2 z" t$ R: _% r* y* USQL> @plustrce
' B2 K' ?/ n! w9 ^+ tSQL>
: f; v8 i% `+ O, }  [& q& YSQL> drop role plustrace;
drop role plustrace
*
  R+ H) L7 s1 m6 X$ MERROR at line 1:
, S& g7 x. B- P+ r) Y0 f- }3 uORA-01919: role 'PLUSTRACE' does not exist
" i( p  A4 @& U- }

% O/ a3 h! M5 Z; Y+ q; I8 iSQL> create role plustrace;

# i% o9 S2 h% e* @7 k% lRole created.

: a3 |# t- E5 n" V2 cSQL>
' g6 k5 S/ h3 \SQL> grant select on v_$sesstat to plustrace;
/ R! S1 ]. y9 F
Grant succeeded.
/ F& N$ X, T. p, N
* a; @. ?, w. ]8 A% |SQL> grant select on v_$statname to plustrace;
) h- P5 ]7 y$ L4 T) t& m6 x! _( p
Grant succeeded.
7 F0 t3 K/ I8 ?/ \1 y6 [, ^
SQL> grant select on v_$mystat to plustrace;

, B! f- t; ]9 k0 p3 yGrant succeeded.
1 y% A. h6 [3 N
SQL> grant plustrace to dba with admin option;

Grant succeeded.
4 c4 ]. V- w& ~, [! Y
SQL>
SQL> set echo off
SQL> grant plustrace to public;

Grant succeeded.

5 V/ j. I2 z& |1 iSQL> 至此,执行成功。