Thanks for the question, Hubert.

Asked: February 21, 2024 - 1:47 pm UTC

Last updated: February 26, 2024 - 5:46 am UTC

Version: Oracle Database Version 19.20.0.0.0

Viewed 10K+ times! This question is

You Asked

Dear Tom,
As User System connected I want to query a V$ table in a view.
When creating the view I get the error ORA-00942: table or view does not exist.

Example:

create or replace view myview_datafile as select file#, bytes/1024/1024||' MB' as "SIZE" from V$datafile;
                                                                                                *
ERROR at line 1:
ORA-00942: table or view does not exist

Executing the same query with sqlplus works.
Is a special privilege required to make this possible?

Many thanks in advance for a reply.
Hubert

and Connor said...

v$ views are a little different :-)

SQL> conn system/admin
Connected.
SQL> create or replace view myview_datafile as select file#, bytes/1024/1024||' MB' as "SIZE" from V$datafile;
create or replace view myview_datafile as select file#, bytes/1024/1024||' MB' as "SIZE" from V$datafile
                                                                                              *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> conn / as sysdba
Connected.
SQL> grant select on sys.V_$datafile to system;

Grant succeeded.

SQL> conn system/admin
Connected.
SQL> create or replace view myview_datafile as select file#, bytes/1024/1024||' MB' as "SIZE" from V$datafile;

View created.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

READ privilege instead of SELECT privilege.

Rajeshwaran Jeyabal, February 23, 2024 - 11:46 am UTC

It would be more appropriate to have READ privilege than SELECT privilege in these latest generation databases.

sys@PDB1> grant read on V_$datafile to demo;

Grant succeeded.

February 26, 2024 - 5:46 am UTC

I dont think anyone is going to be locking these tables any time soon

SQL> select * from v$datafile for update;
select * from v$datafile for update
              *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

READ privilege instead of SELECT privilege.

More to Explore

PL/SQL demos

PL/SQL docs

Query values from v$.. View in a view

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

READ privilege instead of SELECT privilege.

More to Explore

PL/SQL demos

PL/SQL docs