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. - A
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 theSELECT ANY TABLEprivilege. This privilege is assigned to theDBArole when the system is initially installed. - A
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.
| Column | Description |
| FILE# | Datafile identifier number (to find the filename, query DBA_DATA_FILES or V$DBFILE) |
| BLOCK# | Block number |
| CLASS# | Class number |
| STATUS | Status 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 | |
| XNC | Number of PCM x to null lock conversions due to contention with another instance. This column is obsolete and maintained for backward compatibility. |
| LOCK_ELEMENT_ADDR | 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_NAME | The 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_CLASS | The 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_READS | Number 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_WRITES | Number 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 |
| DIRTY | Y – block modified |
| TEMP | Y – temporary block |
| PING | Y – block pinged |
| STALE | Y – block is stale |
| DIRECT | Y – direct block |
| NEW | Always set to N. This column is obsolete and maintained for backward compatibility. |
| OBJD | Database object number of the block that the buffer represents |
| TS# | Tablespace number of block |
