What's the general difference between a
v$ view and its
dba_ equivalent? Take, for example,
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$TABLESPACEis a view on
X$KCCTS, which is an internal memory structure.
DBA_TABLESPACESis a view on the data dictionary table
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...