9

What's the general difference between a v$ view and its dba_ equivalent? Take, for example, v$tablespace and dba_tablespaces.

13

The easiest way to think of it is:

  • DBA_ / USER_ / ALL_ views are built on the data dictionary - they're not available if the database is not mounted and opened.

  • V$ views tend to run against the instance, and therefore may be available if the database is not mounted, or is not mounted and opened, depending on the nature of the view.

Using your example:

  • V$TABLESPACE is a view on X$KCCTS, which is an internal memory structure.
  • DBA_TABLESPACES is a view on the data dictionary table SYS.TS$
3

Besides the differences that Adam Musch listed, there are a few more differences between dba_ views and v$ views that are worth mentioning since they are potential gotchas if you aren't aware of these differences:

1) Most (but not quite all) v$ views aren't technically views at all, but are synonyms to v_$ views. This is an important distinction since you can't grant/revoke permissions against synonyms:

sqlplus / as sysdba

grant select on v$tablespace to user1;
   SQL Error: ORA-02030: can only select from fixed tables/views

select table_name from dba_synonyms where synonym_name = 'V$TABLESPACE';
   TABLE_NAME
   -------------
   V_$TABLESPACE

grant select on V_$TABLESPACE to user1;
   grant succeeded.

2) You can run flashback queries against dba_ views. However, running flashback queries against v$ views returns current data (12.1 docs on Using Oracle Flashback Technology):

You cannot retrieve past data from a dynamic performance (V$) view. A query on such a view returns current data.

You can perform queries on past data in static data dictionary views, such as *_TABLES.

As Adam Musch pointed out, v$ views run directly against the instance whereas dba_ views run against the data dictionary. Once you understand that, it makes sense why this limitation is in place. Still though, I really wish flashback query against v$ views would return an error instead of silently not working as this gotcha can remain unnoticed for quite some time...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.