The dynamic performance data dictionary views (the $V views) primarily cover information about the instance and information that the instance has about the database. These views are referred to as dynamic views because their contents change based upon how the instance is performing. The contents respond to the total workload, rather than to any one specific SQL statement or command.
Table 14.3 provides a quick reference for some commonly used dynamic performance data dictionary views.
If You Need to Find Information About... |
Use These Data Dictionary Views |
List of features in use that preclude being able to downgrade Oracle releases |
V$COMPATIBILITY V$COMPATSEG |
Current Oracle options loaded |
V$OPTION |
Current Oracle release levels |
V$VERSION |
Parallel Query performance |
V$PQ_TQSTAT |
Active database pipes |
V$DB_PIPES |
Active database links |
V$DBLINK |
List of all background processes |
V$BGPROCESS |
Currently enabled privileges |
V$ENABLEDPRIVS |
List of all dynamic performance data dictionary views |
V$FIXED_TABLE |
Definitions of the dynamic performance data dictionary views |
V$FIXED_VIEW_DEFINITIONS |
List of all users defined in the password file |
V$PWFILE_USERS |
Contents of RMAN backup sets |
V$BACKUP_DATAFILE |
Location of archived redo logs |
V$ARCHIVED_LOG |
Location of RMAN backup sets |
V$BACKUP_SET |
Number of threads in the database |
V$THREAD |
Current usage of fixed resources specified in the INIT.ORA file. |
V$RESOURCE_LIMIT |
Information about active rollback segments |
V$ROLLNAME V$ROLLSTAT |
Current temporary segments used for sorting |
V$SORT_SEGMENT V$SORT_USAGE |
Information about who is currently connected to the instance |
V$SESSION V$PROCESS |
Size of the SGA |
V$SGA |
Allocation of space within the SGA |
V$SGASTAT |
Current SQL statements |
V$SQLTEXT V$SQLTEXT_WITH_NEWLINES |
Current values of INIT.ORA parameters |
V$PARAMETER V$SYSTEM_PARAMETER |
Current NLS parameters |
V$NLS_PARAMETERS |
List of all datafiles |
V$DATAFILE |
List of all outstanding locks and latches |
V$LOCK V$LATCH |
Multi-Threaded Server processes |
V$DISPATCHER |
Oracle Parallel Server locks |
V$DLM_LOCKS V$DLM_LATCH |