在做set autot 的時候出現關于plan_table error ORA-01039

09:21:28 SQL> conn system/password
Connected.
09:28:50 SQL> @$ORACLE_HOME/rdbms/admin/utlxplan

Table created.

09:29:46 SQL> grant all on plan_table to public;

Grant succeeded.

09:31:02 SQL> create public synonym plan_table for plan_table;

Synonym created.

09:31:29 SQL> conn scott/tiger
Connected.
09:31:40 SQL> set autot trace
09:31:52 SQL> select table_name from user_tables;


Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view


SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         62  consistent gets
          0  physical reads
          0  redo size
        444  bytes sent via SQL*Net to client
        498  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

09:32:14 SQL>

 

 

 

 

其实不需要搞得这么复杂。
SQL> create user hao identified by hao;

User created.
SQL> @?/sqlplus/admin/plustrce.sql
SQL> --
SQL> -- Copyright (c) Oracle Corporation 1995, 2002.  All Rights Reserved.
SQL> --
SQL> -- NAME
SQL> --   plustrce.sql
SQL> --
SQL> -- DESCRIPTION
SQL> --   Creates a role with access to Dynamic Performance Tables
SQL> --   for the SQL*Plus SET AUTOTRACE ... STATISTICS command.
SQL> --   After this script. has been run, each user requiring access to
SQL> --   the AUTOTRACE feature should be granted the PLUSTRACE role by
SQL> --   the DBA.
SQL> --
SQL> -- USAGE
SQL> --   sqlplus "sys/knl_test7 as sysdba" @plustrce
SQL> --
SQL> --   Catalog.sql must have been run before this file is run.
SQL> --   This file must be run while connected to a DBA schema.
SQL>
SQL> set echo on
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 connect,resource,plustrace to hao;

Grant succeeded.


sqlplus hao/hao

SQL> create table test(id number);

Table created.

SQL> set autotrace on
SQL> select * from test;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 217508114

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         24  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        319  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13165828/viewspace-607260/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13165828/viewspace-607260/