V$ View & Data Dictionary View on Oracle

Dynamic Performance (V$) Views: The Oracle Server contains a set of underlying views that are maintained by the server and accessible to the database administrator user SYS. These views are called dynamic performance views because they are continuously updated while a database is open and in use, and their contents relate primarily to performance.

Data Dictionary View: Data dictionary tables are not directly accessible, but you can access information in them through data dictionary views. To list the data dictionary views available to you, query the view DICTIONARY.

Many data dictionary tables have three corresponding views:

  • An ALL_ view displays all the information accessible to the current user, including information from the current user’s schema as well as information from objects in other schemas, if the current user has access to those objects by way of grants of privileges or roles.
  • DBA_ view displays all relevant information in the entire database. DBA_ views are intended only for administrators. They can be accessed only by users with the SELECT ANY TABLE privilege. This privilege is assigned to the DBA role when the system is initially installed.
  • USER_ view displays all the information from the schema of the current user. No special privileges are required to query these views.

Dynamic Performance (V$) Views:

SQL> SELECT NAME, TYPE FROM V$FIXED_TABLE WHERE NAME LIKE 'V$%';

677 rows selected.

SQL> SELECT NAME, TYPE FROM V$FIXED_TABLE WHERE NAME LIKE 'V$SESSION%';

NAME                           TYPE
------------------------------ -----
V$SESSION                      VIEW
V$SESSION_CONNECT_INFO         VIEW
V$SESSION_WAIT_HISTORY         VIEW
V$SESSION_BLOCKERS             VIEW
V$SESSION_WAIT                 VIEW
V$SESSION_WAIT_CLASS           VIEW
V$SESSION_EVENT                VIEW
V$SESSION_CURSOR_CACHE         VIEW
V$SESSION_LONGOPS              VIEW
V$SESSION_OBJECT_CACHE         VIEW
V$SESSION_FIX_CONTROL          VIEW
V$SESSION_CLIENT_RESULT_CACHE  VIEW
V$SESSIONS_COUNT               VIEW

13 rows selected.

Data Dictionary View:

SQL> COL OWNER FOR A10
SQL> COL VIEW_NAME FOR A30
SQL> COL VIEW_TYPE FOR A30

SQL> SELECT OWNER,VIEW_NAME,VIEW_TYPE FROM DBA_VIEWS
    WHERE OWNER='SYS' AND VIEW_NAME LIKE 'DBA_TABLE%';

OWNER                VIEW_NAME                      VIEW_TYPE
-------------------- ------------------------------ --------------------
SYS                  DBA_TABLESPACE_USAGE_METRICS
SYS                  DBA_TABLESPACE_THRESHOLDS
SYS                  DBA_TABLESPACE_GROUPS
SYS                  DBA_TABLESPACES
SYS                  DBA_TABLES
SQL> SELECT OWNER,VIEW_NAME,VIEW_TYPE FROM DBA_VIEWS
WHERE OWNER='SYS' AND VIEW_NAME LIKE '%DATABASE%';  

OWNER      VIEW_NAME                      VIEW_TYPE
---------- ------------------------------ ------------------------------
SYS        V_$FLASHBACK_DATABASE_STAT
SYS        V_$FLASHBACK_DATABASE_LOGFILE
SYS        V_$FLASHBACK_DATABASE_LOG
SYS        V_$DATABASE_KEY_INFO
SYS        V_$DATABASE_INCARNATION
SYS        V_$DATABASE_BLOCK_CORRUPTION
SYS        V_$DATABASE
SYS        NLS_DATABASE_PARAMETERS
SYS        INT$DBA_HIST_DATABASE_INSTANCE
SYS        GV_$FLASHBACK_DATABASE_STAT
SYS        GV_$FLASHBACK_DATABASE_LOGFILE
SYS        GV_$FLASHBACK_DATABASE_LOG
SYS        GV_$DATABASE_KEY_INFO
SYS        GV_$DATABASE_INCARNATION
SYS        GV_$DATABASE_BLOCK_CORRUPTION
SYS        GV_$DATABASE
SYS        DBA_STREAMS_TP_DATABASE
SYS        DBA_SCHEDULER_REMOTE_DATABASES
SYS        DBA_ROLLING_DATABASES
SYS        DBA_REGISTRY_DATABASE
SYS        DBA_HIST_DATABASE_INSTANCE
SYS        DBA_CAPTURE_PREPARED_DATABASE
SYS        DATABASE_SUMMARY
SYS        DATABASE_PROPERTIES
SYS        DATABASE_EXPORT_PATHS
SYS        DATABASE_EXPORT_OBJECTS
SYS        DATABASE_COMPATIBLE_LEVEL
SYS        CDB_STREAMS_TP_DATABASE
SYS        CDB_SCHEDULER_REMOTE_DATABASES
SYS        CDB_ROLLING_DATABASES
SYS        CDB_REGISTRY_DATABASE
SYS        CDB_HIST_DATABASE_INSTANCE
SYS        CDB_CAPTURE_PREPARED_DATABASE
SYS        ALL_SCHEDULER_REMOTE_DATABASES
SYS        ALL_CAPTURE_PREPARED_DATABASE

35 rows selected.
SQL> SELECT OWNER,VIEW_NAME,VIEW_TYPE FROM DBA_VIEWS
WHERE OWNER='SYS' AND VIEW_NAME LIKE '%INSTANCE%';  2

OWNER      VIEW_NAME                      VIEW_TYPE
---------- ------------------------------ ------------------------------
SYS        V_$PX_INSTANCE_GROUP
SYS        V_$INSTANCE_RECOVERY
SYS        V_$INSTANCE_PING
SYS        V_$INSTANCE_LOG_GROUP
SYS        V_$INSTANCE_CACHE_TRANSFER
SYS        V_$INSTANCE
SYS        V_$AQ_CROSS_INSTANCE_JOBS
SYS        V_$ACTIVE_INSTANCES
SYS        USER_ADDM_INSTANCES
SYS        NLS_INSTANCE_PARAMETERS
SYS        KU$_PROCACT_INSTANCE_VIEW      KU$_PROCACT_INSTANCE_T
SYS        KU$_INSTANCE_CALLOUT_VIEW      KU$_CALLOUT_T
SYS        INT$DBA_HIST_PDB_INSTANCE
SYS        INT$DBA_HIST_INSTANCE_RECOVERY
SYS        INT$DBA_HIST_DATABASE_INSTANCE
SYS        GV_$PX_INSTANCE_GROUP
SYS        GV_$INSTANCE_RECOVERY
SYS        GV_$INSTANCE_PING
SYS        GV_$INSTANCE_LOG_GROUP
SYS        GV_$INSTANCE_CACHE_TRANSFER
SYS        GV_$INSTANCE
SYS        GV_$AQ_CROSS_INSTANCE_JOBS
SYS        GV_$ACTIVE_INSTANCES
SYS        EXU81PROCOBJINSTANCE
SYS        DBA_SECUREFILE_LOG_INSTANCES
SYS        DBA_RSRC_INSTANCE_CAPABILITY
SYS        DBA_HIST_PDB_INSTANCE
SYS        DBA_HIST_INSTANCE_RECOVERY
SYS        DBA_HIST_DATABASE_INSTANCE
SYS        DBA_ADVISOR_DIR_INSTANCES
SYS        DBA_ADDM_INSTANCES
SYS        CDB_SECUREFILE_LOG_INSTANCES
SYS        CDB_RSRC_INSTANCE_CAPABILITY
SYS        CDB_HIST_PDB_INSTANCE
SYS        CDB_HIST_INSTANCE_RECOVERY
SYS        CDB_HIST_DATABASE_INSTANCE
SYS        CDB_ADVISOR_DIR_INSTANCES
SYS        CDB_ADDM_INSTANCES

38 rows selected.

RMAN & Backup Related:

SQL> SELECT VIEW_NAME FROM DBA_VIEWS
WHERE OWNER='SYS' AND VIEW_NAME LIKE '%RMAN%' OR VIEW_NAME LIKE '%BACKUP%';  2

VIEW_NAME
------------------------------
V_$RMAN_ENCRYPTION_ALGORITHMS
V_$RMAN_BACKUP_TYPE
V_$UNUSABLE_BACKUPFILE_DETAILS
V_$BACKUP_COPY_SUMMARY
V_$BACKUP_SPFILE_SUMMARY
V_$BACKUP_ARCHIVELOG_SUMMARY
V_$BACKUP_CONTROLFILE_SUMMARY
V_$BACKUP_DATAFILE_SUMMARY
V_$BACKUP_SET_SUMMARY
V_$BACKUP_SPFILE_DETAILS
V_$BACKUP_ARCHIVELOG_DETAILS
V_$BACKUP_CONTROLFILE_DETAILS
V_$BACKUP_DATAFILE_DETAILS
V_$BACKUP_COPY_DETAILS
V_$BACKUP_PIECE_DETAILS
V_$BACKUP_SET_DETAILS
V_$RMAN_BACKUP_JOB_DETAILS
V_$RMAN_BACKUP_SUBJOB_DETAILS
V_$BACKUP_FILES
GV_$BACKUP_NONLOGGED
V_$BACKUP_NONLOGGED
GV_$RMAN_COMPRESSION_ALGORITHM
V_$RMAN_COMPRESSION_ALGORITHM
GV_$RMAN_OUTPUT
V_$RMAN_OUTPUT
V_$RMAN_STATUS
GV_$RMAN_CONFIGURATION
GV_$BACKUP_ASYNC_IO
GV_$BACKUP_SYNC_IO
GV_$BACKUP_DEVICE
GV_$BACKUP_CORRUPTION
GV_$BACKUP_REDOLOG
GV_$BACKUP_SPFILE
GV_$BACKUP_DATAFILE
GV_$BACKUP_PIECE
GV_$BACKUP_SET
GV_$BACKUP
V_$RMAN_CONFIGURATION
V_$BACKUP_ASYNC_IO
V_$BACKUP_SYNC_IO
V_$BACKUP_DEVICE
V_$BACKUP_CORRUPTION
V_$BACKUP_REDOLOG
V_$BACKUP_SPFILE
V_$BACKUP_DATAFILE
V_$BACKUP_PIECE
V_$BACKUP_SET
V_$BACKUP

48 rows selected.

Oracle Concurrency and SQL Tuning:

V$LOCK
V$SQL
V$SQLAREA
V$SESSTAT
V$MYSTAT
V$SESS_IO
V$SYSSTAT
V$STATNAME
V$OSSTAT
V$ACTIVE_SESSION_HISTORY
V$ACTIVE_SESS_POOL_MTH
V$SESSION_WAIT
V$SESSION_WAIT_CLASS
V$SYSTEM_WAIT_CLASS
V$TRANSACTION
V$LOCKED_OBJECT
V$LATCH
V$LATCH_CHILDREN
V$LATCH_PARENT
V$LATCHNAME
V$LATCHHOLDER
V$LATCH_MISSES
V$ENQUEUE_LOCK
V$TRANSACTION_ENQUEUE
V$SYS_OPTIMIZER_ENV
V$SES_OPTIMIZER_ENV
V$SQL_OPTIMIZER_ENV
V$SQL_PLAN
V$SQL_PLAN_STATISTICS
V$SQL_PLAN_STATISTICS_ALL

DataGuard Related:

SQL> SELECT OWNER,VIEW_NAME,VIEW_TYPE FROM DBA_VIEWS
WHERE OWNER='SYS' AND VIEW_NAME LIKE '%GUARD%' OR VIEW_NAME LIKE '%STANDBY%' OR VIEW_NAME LIKE '%LOGSTDBY%'; 

DataGuard Related Important View:

V_$DATAGUARD_STATUS
V_$DATAGUARD_STATS
V_$DATAGUARD_CONFIG
GV_$DATAGUARD_STATUS
GV_$DATAGUARD_STATS
GV_$DATAGUARD_CONFIG
V$MANAGED_STANDBY
V$LOGSTDBY
V$LOGSTDBY_STATS
V$LOGSTDBY_TRANSACTION
V$LOGSTDBY_PROCESS
V$LOGSTDBY_PROGRESS
V$LOGSTDBY_STATE
SQL> SELECT OWNER,VIEW_NAME,VIEW_TYPE FROM DBA_VIEWS
WHERE OWNER='SYS' AND VIEW_NAME LIKE '%ARCHIVE%';  2

OWNER      VIEW_NAME                      VIEW_TYPE
---------- ------------------------------ ------------------------------
SYS        V_$PROXY_ARCHIVELOG_SUMMARY
SYS        V_$PROXY_ARCHIVELOG_DETAILS
SYS        V_$PROXY_ARCHIVEDLOG
SYS        V_$FOREIGN_ARCHIVED_LOG
SYS        V_$BACKUP_ARCHIVELOG_SUMMARY
SYS        V_$BACKUP_ARCHIVELOG_DETAILS
SYS        V_$ARCHIVE_PROCESSES
SYS        V_$ARCHIVE_GAP
SYS        V_$ARCHIVE_DEST_STATUS
SYS        V_$ARCHIVE_DEST
SYS        V_$ARCHIVED_LOG
SYS        V_$ARCHIVE
SYS        USER_FLASHBACK_ARCHIVE_TABLES
SYS        USER_FLASHBACK_ARCHIVE
SYS        GV_$PROXY_ARCHIVEDLOG
SYS        GV_$FOREIGN_ARCHIVED_LOG
SYS        GV_$ARCHIVE_PROCESSES
SYS        GV_$ARCHIVE_GAP
SYS        GV_$ARCHIVE_DEST_STATUS
SYS        GV_$ARCHIVE_DEST
SYS        GV_$ARCHIVED_LOG
SYS        GV_$ARCHIVE
SYS        DBA_REGISTERED_ARCHIVED_LOG
SYS        DBA_FLASHBACK_ARCHIVE_TS
SYS        DBA_FLASHBACK_ARCHIVE_TABLES
SYS        DBA_FLASHBACK_ARCHIVE
SYS        CDB_REGISTERED_ARCHIVED_LOG
SYS        CDB_FLASHBACK_ARCHIVE_TS
SYS        CDB_FLASHBACK_ARCHIVE_TABLES
SYS        CDB_FLASHBACK_ARCHIVE

30 rows selected.

RAC Related:

V$CLUSTER_INTERCONNECTS
V$CONFIGURED_INTERCONNECTS
V$DYNAMIC_REMASTER_STATS
V$DLM_MISC
V$DLM_LATCH
V$DLM_CONVERT_LOCAL
V$DLM_CONVERT_REMOTE
V$GES_ENQUEUE
V$GES_BLOCKING_ENQUEUE
V$DLM_ALL_LOCKS
V$DLM_LOCKS
V$DLM_RESS
V$GLOBAL_BLOCKED_LOCKS

List of all Events:

SQL> SELECT NAME, WAIT_CLASS FROM V$EVENT_NAME;

1650 rows selected.

List of all events related with Clusterware:

SQL> SELECT NAME, WAIT_CLASS FROM V$EVENT_NAME WHERE WAIT_CLASS ='Cluster';

NAME                                               WAIT_CLASS
-------------------------------------------------- ------------------------------
retry contact SCN lock master                      Cluster
remote log force - buffer update                   Cluster
remote log force - buffer read                     Cluster
remote log force - buffer send                     Cluster
remote log force - SCN range                       Cluster
remote log force - session cleanout                Cluster
gc buffer busy acquire                             Cluster
gc buffer busy release                             Cluster
pi renounce write complete                         Cluster
remote log force - log switch/recovery             Cluster
gc current request                                 Cluster
gc cr request                                      Cluster
gc cr disk request                                 Cluster
gc cr multi block request                          Cluster
gc current multi block request                     Cluster
gc block recovery request                          Cluster
gc imc multi block request                         Cluster
gc imc multi block quiesce                         Cluster
gc cr block 2-way                                  Cluster
gc cr block 3-way                                  Cluster
gc cr block busy                                   Cluster
gc cr block congested                              Cluster
gc cr failure                                      Cluster
gc cr block lost                                   Cluster
gc cr block unknown                                Cluster
gc current block 2-way                             Cluster
gc current block 3-way                             Cluster
gc current block busy                              Cluster
gc current block congested                         Cluster
gc current retry                                   Cluster
gc current block lost                              Cluster
gc current split                                   Cluster
gc current block unknown                           Cluster
gc cr grant 2-way                                  Cluster
gc cr grant busy                                   Cluster
gc cr grant congested                              Cluster
gc cr grant unknown                                Cluster
gc cr disk read                                    Cluster
gc cr grant ka                                     Cluster
gc cr grant cluster flash cache read               Cluster
gc cr cluster flash cache read                     Cluster
gc cr flash cache copy                             Cluster
gc current grant 2-way                             Cluster
gc current grant busy                              Cluster
gc current grant congested                         Cluster
gc current grant unknown                           Cluster
gc current grant ka                                Cluster
gc current grant cluster flash cache read          Cluster
gc freelist                                        Cluster
gc remaster                                        Cluster
gc quiesce                                         Cluster
gc recovery                                        Cluster
gc flushed buffer                                  Cluster
gc send complete                                   Cluster
gc current cancel                                  Cluster
gc cr cancel                                       Cluster
gc assume                                          Cluster
gc domain validation                               Cluster
gc recovery free                                   Cluster
gc recovery quiesce                                Cluster
gc claim                                           Cluster
gc cancel retry                                    Cluster
ASM PST query : wait for [PM][grp][0] grant        Cluster
lock remastering                                   Cluster

64 rows selected.

V$BH – Displays the status and number of pings for every buffer in the SGA.

ColumnDescription
FILE#Datafile identifier number (to find the filename, query DBA_DATA_FILES or V$DBFILE)
BLOCK#Block number
CLASS#Class number
STATUSStatus of the buffer:
free – Not currently in use
xcur – Exclusive
scur – Shared current
cr – Consistent read
read – Being read from disk
mrec – In media recovery mode
irec – In instance recovery mode
XNCNumber of PCM x to null lock conversions due to contention with another instance. This column is obsolete and maintained for backward compatibility.
LOCK_ELEMENT_ADDRAddress of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock.
LOCK_ELEMENT_NAMEThe address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock.
LOCK_ELEMENT_CLASSThe address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock.
FORCED_READSNumber of times the block had to be reread from the cache because another instance has forced it out of this instance’s cache by requesting the lock on the block in exclusive mode
FORCED_WRITESNumber of times GCS had to write this block to cache because this instance had used the block and another instance had requested the lock on the block in a conflicting mode
DIRTYY – block modified
TEMPY – temporary block
PINGY – block pinged
STALEY – block is stale
DIRECTY – direct block
NEWAlways set to N. This column is obsolete and maintained for backward compatibility.
OBJDDatabase object number of the block that the buffer represents
TS#Tablespace number of block