CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
neolei
neolei

Reputation: 1948

Failed to create view contains v$view

In sqlplus under normal schema account (not sys), query select * from v$instance; runs OK. But when create view which references it, error happens:

create or replace view vw_test_instance
as
select * from V$instance;

SQL Error: ORA-01031: insufficient privileges

If switch v$instance to any normal table like select * from dual;, then the view created with no problem.

Oracle version:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE   11.2.0.4.0  Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

The back ground is I'm upgrading a database run on 10g to 11g, things used to work on 10g just broken on 11g... Can I fix it without touching source code of views? Appreciated!

Upvotes: 0

Views: 733

Answers (2)

APC
APC

Reputation: 146289

"query select * from v$instance; runs OK. But when create view which references it, error happens:"

The error you get - SQL Error: ORA-01031: insufficient privileges - indicates that your user lacks the privileges to create a view.

" things used to work on 10g just broken on 11g"

Clearly on 10g privileges your user had privileges to create views and on 11g they do not.

The most likely explanation is that your user relies on the CONNECT role. In older versions of Oracle this role had several system privileges, including CREATE VIEW. This implicit granting of privileges was always dodgy. However, in 11g this changed and the role now only has CREATE SESSION. All other privileges must be granted explicitly. This is mentioned in the 11g Upgrade docs and explained in depth by the 11g Security Guide Find out more.

Upvotes: 0

Avrajit Roy
Avrajit Roy

Reputation: 3303

This is related to user GRANTS.

1. GRANT SELECT ON V$INSTANCE TO <USER>; --> Will be provided by SYSDBA 

2. Then try. It will definitley help.

This link will also help you to understand why this kind of behaviour occurs.

v$views Grant

Upvotes: 0

Related Questions