创建视图时ORA-01031

I started reading a Tom Kyte's book and immediately ran into a problem :) - sql is executed just fine but CREATE VIEW based on that sql returns ORA-01031.

我开始阅读Tom Kyte的书并立即遇到问题:) - sql执行得很好但基于该sql的CREATE VIEW返回ORA-01031。

Commands below were executed on behalf of "system" user:

以下命令代表“system”用户执行:

create or replace view stats
as select 'STAT...' || a.name name, b.value
      from v$statname a, v$mystat b
     where a.statistic# = b.statistic#
    union all
    select 'LATCH.' || name,  gets
      from v$latch
    union all
    select 'STAT...Elapsed Time', hsecs from v$timer;

ERROR at line 3: ORA-01031: insufficient privileges

第3行的错误:ORA-01031:权限不足

However executing the sql renders no errors:

但是,执行sql会导致无错误:

select 'STAT...' || a.name name, b.value
      from v$statname a, v$mystat b
     where a.statistic# = b.statistic#
    union all
    select 'LATCH.' || name,  gets
      from v$latch
    union all
    select 'STAT...Elapsed Time', hsecs from v$timer;

1 个解决方案

#1

The system user lacks select any dictionary privilege, or select on [v_$mystat|v_$statname | v_$latch] object privilege granted directly(not through the dba role), not the create view one. That's the main reason why you cannot create that view in the system schema. As soon as you grant one of the mentioned above privileges to the system user, you will be able to create your view successfully, but, try to never create user objects in system schemas, whether it's sys or system. Create separate user, grant appropriate privileges and do whatever you want to do.

系统用户缺少选择任何字典权限,或选择[v_ $ mystat | v_ $ statname |直接授予v_ $ latch]对象权限(不是通过dba角色),而不是create view one。这是您无法在系统架构中创建该视图的主要原因。一旦您向系统用户授予上述特权之一,您就可以成功创建视图,但是,尝试永远不要在系统模式中创建用户对象,无论是sys还是system。创建单独的用户,授予适当的权限并执行您想要执行的任何操作。

SQL> show user;
USER is "SYSTEM"

SQL> create or replace view v_1 as
  2    select *
  3      from v$mystat;

from v$mystat
         *
ERROR at line 3:
ORA-01031: insufficient privileges 


SQL> conn / as sysdba
Connected.

SQL> grant select any dictionary to system;

Grant succeeded.

SQL> conn system/pwd -- connect as system

Connected.

SQL> create or replace view v_1 as
  2    select *
  3      from v$mystat;

View created.

SQL> drop view v_1;

View dropped.

1 个解决方案

#1

更多相关文章

随机推荐