Skip to Main Content
  • Questions

  • Query values from v$.. View in a view

Breadcrumb

Question and Answer

Connor McDonald

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 1000+ times

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)

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.

Connor McDonald
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

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library