CODEWITHSUNDEEP
sqloracle-database
ValeryC
ValeryC

Reputation: 486

ORA-01031 when creating a view

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.

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

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

However executing the sql renders no errors:

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;

Upvotes: 2

Views: 2010

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27251

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.

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.

Upvotes: 3

Related Questions