用一个实例讲解如何生成autotrace权限

一、配置autotrace

1、cd [Oracle_HOME]/rdbms/admin;

2、以system用户登陆;

3、执行@utlxplan;

4、执行create public synonym plan_table for plan_table;

5、执行 grant all on plan_table to public;

二、创建plustrace角色

1、cd [ORACLE_HOME]/sqlplus/admin;

2、以sys或sysdba登陆;

3、运行@plustrce;

4、执行grant plustrace to public;

注：也可以将public指定为某个用户。

[oracle@olivenan oracle]$ cd $ORACLE_HOME/rdbms/admin/

[oracle@olivenan admin]$ sqlplus system/oracle

SQL*Plus: Release 9.2.0.4.0 - PRoduction on Wed Jun 27 11:00:20 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

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

SQL> create public synonym plan_table for plan_table;

Synonym created.

SQL> grant all on plan_table to public;

Grant succeeded.

SQL> exit

Disconnected from Oracle9i 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

[oracle@olivenan admin]$ cd $ORACLE_HOME/sqlplus/admin

[oracle@olivenan admin]$ sysplus "/as sysdba"

-bash: sysplus: command not found

[oracle@olivenan admin]$ sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Jun 27 11:04:55 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

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

SQL> grant plustrace to public;

Grant succeeded.

SQL>

至此,执行成功。