Oracle | Dynamic Performance Views

Oracle Dynamic Performance Views Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Page Notes V$ view are views based on X$ arrays. The GV$ views are Global V$ views that have, as their first column, the instance identifier (INST_ID). You should always use GV$ rather than V$ where-ever possible. To examine their SQL definitions open the file $ORACLE_HOME/rdbms/admin/cdfixed.sql.

Dynamic Performance View Growth

Version	Number Views
6	23
7.1	72
8.1	185
9.2	259
10.1	340
10.2	372
11.1	484
11.2	525
12.1	606
12.2	746
18.3	859
19.3	877
20.3	911
21.1	925

Categories

Sections
Advanced Queuing (AQ)	Feature Usage	Parameters
Advisors	Fixed Views	Processes
Analytic Views	Flashback Database
Archive Logging	Flashback Recovery Area	RAC
ASH	Flashback Transaction	Redo Logs
ASM	GoldenGate	Resource Usage
Backup & Recovery	Hang Detection	Result Cache
Buffers	Health Management	Rollback/Undo Segments
Caches	I/O	Rules
Connection Pool	In-Memory Database	Security & Privileges
Container Database	Instance	Services
Control File	Java & Java Script	Sessions
Cursors & SQL Statements	Keywords	Shared Server
Data Guard & Standbys	Latches & Locks	Storage
Database Links	Log Miner	Streams
Data Pump	Materialized View Refresh	System Statistics
Diagnostics	Memory Utilization	Temp Space
Direct Loader	Metrics	Transportable Platform
Direct NFS	Miscellaneous	Waits
Edition Based Redefinition	OLAP	Workload Management
EQ	Optimizer	Workload Replay
Event Monitor	Oracle File System (OFS)	XStreams
Exadata	Parallel Query

Query to identify V$ views not duplicated as GV$ views SELECT object_name FROM dba_objects_ae WHERE object_name LIKE 'V%$%' AND object_type = 'VIEW' MINUS SELECT SUBSTR(object_name, 2) FROM dba_objects_ae WHERE object_name LIKE 'GV%$%' AND object_type = 'VIEW' ORDER BY 1;

GV$ Reverse Engineering

V$ objects, for the most part, are derived from a V$ parent -- the following demo shows how to find the X$ source for GV$ objects from which V$ objects are derived SELECT view_name, view_definition FROM v$fixed_view_definition WHERE UPPER(view_definition) LIKE '%X$KKSBV%'; VIEW_NAME VIEW_DEFINITION -------------------- --------------------------------------------------------------------------- GV$SQL_BIND_METADATA select inst_id,kglhdadr, position, kkscbndt, kkscbndl, kkscbnda, kksbvnnam, con_id from x$kksbv

Advanced Queuing

gv$aq	gv$aq_partition_stats
gv$aq1	gv$aq_remote_dequqeue_affinity
gv$aq_background_coordinator	gv$aq_server_pool
gv$aq_bmap_nondur_subscribers	gv$aq_sharded_subscriber_stat
gv$aq_cached_subshards	gv$aq_subscriber_load
gv$aq_cross_instance_jobs	gv$aq_uncached_subshards
gv$aq_dequeue_sessions	gv$buffered_publishers
gv$aq_inactive_subshards	gv$buffered_queues
gv$aq_ipc_active_msgs	gv$buffered_subscribers
gv$aq_ipc_msg_stats	gv$persistent_publishers
gv$aq_ipc_pending_msgs	gv$persistent_qmn_cache
gv$aq_job_coordinator	gv$persistent_queues
gv$aq_message_cache	gv$persistent_subscribers
gv$aq_message_cache_advice	gv$qmon_coordinator_stats
gv$aq_message_cache_stat	gv$qmon_server_stats
gv$aq_msgbm	gv$qmon_tasks
gv$aq_nondur_registrations	gv$qmon_task_stats
gv$aq_nondur_subscriber	gv$queue
gv$aq_nondur_subscriber_lwm	gv$queueing_mth
gv$aq_notification_clients

Advisors

gv$advisor_current_sqlplan	gv$sga_target_advice
gv$advisor_progress	gv$shared_pool_advice
gv$java_pool_advice	v$stats_advisor_actions
gv$memory_target_advice	v$stats_advisor_findings
gv$mttr_target_advice	v$stats_advisor_rationales
gv$pga_target_advice	v$stats_advisor_recs
gv$pga_target_advice_histogram	v$stats_advisor_rules
gv$px_buffer_advice

Analytic Views

gv$av_btt_cache	gv$mdx_cursor_dependency
gv$mdx_cursors

Archive Logging

gv$archive	gv$archive_processes
gv$archived_log	gv$proxy_archivedlog
gv$archive_dest	v$proxy_archivelog_details
gv$archive_dest_status	v$proxy_archivelog_summary
gv$archive_gap

ASH

gv$active_session_history	gv$ash_info
gv$all_active_session_history	gv$max_active_sess_target_mth

ASM

gv$asm_acfsautoresize	gv$asm_client
gv$asm_acfsrepl	gv$asm_dbclone_info
gv$asm_acfsrepltag	gv$asm_disk
gv$asm_acfssnapshots	gv$asm_diskgroup
gv$asm_acfstag	gv$asm_diskgroup_sparse
gv$asm_acfsvolumes	gv$asm_diskgroup_stat
gv$asm_acfs_encryption_info	gv$asm_disk_iostat
gv$asm_acfs_security_info	gv$asm_disk_sparse
gv$asm_acfs_sec_admin	gv$asm_disk_sparse_stat
gv$asm_acfs_sec_cmdrule	gv$asm_disk_stat
gv$asm_acfs_sec_realm	gv$asm_estimate
gv$asm_acfs_sec_realm_filter	gv$asm_file
gv$asm_acfs_sec_realm_group	gv$asm_filegroup
gv$asm_acfs_sec_realm_user	gv$asm_filegroup_file
gv$asm_acfs_sec_rule	gv$asm_filegroup_property
gv$asm_acfs_sec_ruleset	gv$asm_filesystem
gv$asm_acfs_sec_ruleset_rule	gv$asm_operation
gv$asm_alias	gv$asm_quotagroup
gv$asm_attribute	gv$asm_template
gv$asm_audit_cleanup_jobs	gv$asm_user
gv$asm_audit_clean_events	gv$asm_usergroup
gv$asm_audit_config_params	gv$asm_usergroup_member
gv$asm_audit_last_arch_ts	gv$asm_volume
gv$asm_audit_load_jobs	gv$asm_volume_stat
v$asm_cache_events

Backup and Recovery (RMAN)

gv$backup	gv$db_transportable_platform
v$backup_archivelog_details	gv$deleted_object
v$backup_archivelog_summary	gv$fast_start_servers
gv$backup_async_io	gv$fast_start_transactions
v$backup_controlfile_details	gv$foreign_datafile_copy
v$backup_controlfile_summary	gv$instance_recovery
v$backup_copy_details	v$proxy_copy_details
v$backup_copy_summary	v$proxy_copy_summary
gv$backup_corruption	gv$proxy_datafile
gv$backup_datafile	v$recovery_area_usage
v$backup_datafile_details	v$recovery_file_dest
v$backup_datafile_summary	gv$recovery_file_status
gv$backup_device	gv$recovery_log
v$backup_files	gv$recovery_progress
gv$backup_nonlogged	gv$recovery_slave
gv$backup_piece	gv$recovery_status
v$backup_piece_details	gv$recover_file
gv$backup_redolog	v$restore_range
gv$backup_set	v$rman_backup_job_details
v$backup_set_details	v$rman_backup_subjob_details
v$backup_set_summary	v$rman_backup_type
gv$backup_spfile	gv$rman_compression_algorithm
v$backup_spfile_details	gv$rman_configuration
v$backup_spfile_summary	v$rman_encryption_algorithms
gv$backup_sync_io	gv$rman_output
v$block_change_tracking	v$rman_status
gv$copy_corruption	gv$shadow_datafile
gv$copy_nonlogged	v$sbt_restore_range
gv$database_block_corruption	gv$tsm_sessions
gv$database_incarnation	v$unusable_backupfile_details
gv$datafile_copy

Buffers

gv$bh	gv$buffer_pool_statistics
gv$buffer_pool

Caches

bsp = block server background process

gv$bsp	gv$librarycache
gv$cache	gv$library_cache_memory
v$cache_lock	gv$map_comp_list
v$cache_transfer	gv$map_element
gv$class_cache_transfer	gv$map_ext_element
gv$class_ping	gv$map_file
gv$cr_block_server	gv$map_file_extent
gv$current_block_server	gv$map_file_io_stack
gv$db_cache_advice	gv$map_library
gv$db_object_cache	gv$map_subelement
gv$gcshvmaster_info	gv$propagation_receiver
gv$gcspfmaster_info	gv$propagation_sender
gv$gcs_lock_state_resolution_history	gv$rowcache
gv$hvmaster_info	gv$rowcache_parent
gv$instance_cache_transfer	gv$rowcache_subordinate
gv$javapool	gv$subcache
gv$java_library_cache_memory	gv$temp_ping

Connection Pool

gv$cpool_cc_info	gv$cpool_conn_info
gv$cpool_cc_stats	gv$cpool_stats

Container Database

gv$clonedfile	gv$con_system_wait_class
gv$containers	gv$con_sys_time_model
gv$container_topology	gv$con_waitclassmetric
gv$con_event_histogram_micro	gv$con_waitclassmetric_history
gv$con_sysmetric	v$diag_pdb_problem
gv$con_sysmetric_history	v$diag_pdb_space_mgmt
gv$con_sysmetric_summary	gv$pdbs
gv$con_sysstat	gv$pdb_incarnation
gv$con_system_event	gv$proxy_pdb_targets

SELECT * FROM gv$container_topology;

      

       INST_ID INSTANCE_NUMBER CON_NAME  OPEN_MODE  CPU_COUNT CON_ID IS_HYBRID_READ_ONLY

      -------- --------------- --------- ---------- ---------- ----- ------------------- 

             1               1 CDB$ROOT  READ WRITE          2     1 NO

             1               1 PDB$SEED  READ ONLY           2     2 NO

             1               1 TEST21P1  READ WRITE          2     3 NO

Control Files

gv$controlfile

gv$controlfile_record_section

Cursors and SQL Statements

gv$all_sql_monitor	gv$sql_diag_repository
gv$all_sql_plan	gv$sql_diag_repository_reason
gv$all_sql_plan_monitor	gv$sql_join_filter
gv$object_dependency	gv$sql_monitor
gv$open_cursor	gv$sql_monitor_sesstat
gv$sql	gv$sql_monitor_statname
gv$sqlarea	gv$sql_optimizer_env
gv$sqlarea_plan_hash	gv$sql_plan
gv$sqlcommand	gv$sql_plan_monitor
gv$sqlstats	gv$sql_plan_statistics
gv$sqlstats_plan_hash	gv$sql_plan_statistics_all
gv$sqltext	gv$sql_redirection
gv$sqltext_with_newlines	gv$sql_reoptimization_hints
gv$sql_bind_capture	gv$sql_shard
gv$sql_bind_data	gv$sql_shared_cursor
gv$sql_bind_metadata	gv$sql_shared_memory
gv$sql_cs_histogram	gv$sql_workarea
gv$sql_cs_selectivity	gv$sql_workarea_active
gv$sql_cs_statistics	gv$sql_workarea_histogram
gv$sql_cursor

Data Guard & Standby Databases

gv$dataguard_config	gv$fs_observer_histogram
gv$dataguard_process	gv$logstdby
gv$dataguard_stats	gv$logstdby_process
v$dataguard_status	gv$logstdby_progress
gv$dg_broker_config	gv$logstdby_state
gv$dg_broker_property	gv$logstdby_stats
gv$dg_broker_property_int	gv$logstdby_transaction
gv$foreign_archived_log	gv$managed_standby
gv$fs_failover_histogram	gv$rfs_thread
gv$fs_failover_observers	gv$standby_log
gv$fs_failover_stats	v$standby_event_histogram

Database Links & Heterogeneous Services

gv$dblink	gv$hs_parameter
gv$hs_agent	gv$hs_session

Data Pump

gv$datapump_job

gv$datapump_session

Diagnostics

v$diag_adr_control	gv$diag_sql_trace_records
v$diag_adr_control_aux	v$diag_sweeperr
v$diag_adr_invalidation	gv$diag_trace_file
v$diag_alert_ext (src x$dbgalertext)	gv$diag_trace_file_contents
v$diag_ams_xaction	gv$diag_user_trace_records
gv$diag_app_trace_file	v$diag_vadr_control
v$diag_critical_error	v$diag_vem_user_actlog
v$diag_dde_user_action	v$diag_vem_user_actlog1
v$diag_dde_user_action_def	v$diag_vhm_run
v$diag_dde_usr_act_param	v$diag_view
v$diag_dde_usr_act_param_def	v$diag_viewcol
v$diag_dde_usr_inc_act_map	v$diag_vincident
v$diag_dde_usr_inc_type	v$diag_vincident_file
v$diag_dfw_config_capture	v$diag_vinc_meter_info
v$diag_dfw_config_item	v$diag_vips_file_copy_log
v$diag_dfw_patch_capture	v$diag_vips_file_metadata
v$diag_dfw_patch_item	v$diag_vips_package_file
v$diag_dfw_purge	v$diag_vips_package_history
v$diag_dfw_purge_item	v$diag_vips_package_main_int
v$diag_diagv_incident	v$diag_vips_package_size
v$diag_dir_ext	v$diag_vips_pkg_file
v$diag_em_diag_job	v$diag_vips_pkg_inc_cand
v$diag_em_target_info	v$diag_vips_pkg_inc_dtl
v$diag_em_user_activity	v$diag_vips_pkg_inc_dtl1
v$diag_incckey	v$diag_vips_pkg_main_problem
v$diag_incident	v$diag_vnot_exist_incident
v$diag_incident_file	v$diag_vpdb_problem
v$diag_inc_meter_config	v$diag_vproblem
v$diag_inc_meter_impt_def	v$diag_vproblem1
v$diag_inc_meter_info	v$diag_vproblem2
v$diag_inc_meter_pk_impts	v$diag_vproblem_bucket
v$diag_inc_meter_summary	v$diag_vproblem_bucket1
gv$diag_info	v$diag_vproblem_bucket_count
v$diag_ips_configuration	v$diag_vproblem_int
v$diag_ips_file_copy_log	v$diag_vproblem_lastinc
v$diag_ips_file_metadata	v$diag_vshowcatview
v$diag_ips_package	v$diag_vshowincb
v$diag_ips_package_file	v$diag_vshowincb_i
v$diag_ips_package_history	v$diag_vtest_exists
v$diag_ips_package_incident	v$diag_v_actinc
v$diag_ips_pkg_unpack_hist	v$diag_v_actprob
v$diag_ips_progress_log	v$diag_v_inccount
v$diag_ips_remote_package	v$diag_v_incfcount
v$diag_log_ext	v$diag_v_inc_meter_info_prob
gv$diag_opt_trace_records	v$diag_v_ipsprbcnt
v$diag_pdb_problem	v$diag_v_ipsprbcnt1
v$diag_pdb_space_mgmt	v$diag_v_nfcinc
v$diag_pickleerr	v$diag_v_swperrcount
v$diag_problem	gv$incmeter_config
v$diag_relmd_ext	gv$incmeter_info
v$diag_sess_opt_trace_records	gv$incmeter_summary
v$diag_sess_sql_trace_records	gv$plsql_debuggable_sessions

Direct Loader

gv$loadistat

gv$loadpstat

Direct NFS & NFS

gv$dnfs_channels	gv$nfs_clients
gv$dnfs_files	gv$nfs_locks
gv$dnfs_servers	gv$nfs_open_files
gv$dnfs_stats

Edition Based Redefinition

gv$editionable_types

gv$eq_cached_partitions	gv$eq_nondur_subscriber_lwm
gv$eq_cross_instance_jobs	gv$eq_partition_stats
gv$eq_dequeue_sessions	gv$eq_remove_dequeue_affinity
gv$eq_inactive_partitions	gv$eq_subscriber_load
gv$eq_message_cache	gv$eq_subscriber_stat
gv$eq_message_cache_advice	gv$eq_subscr_registration_stats
gv$eq_message_cache_stat	gv$eq_uncached_partitions
gv$eq_nondur_subscriber

Event Monitor

gv$emon

gv$subscr_registration_stats

Exadata

gv$cell	gv$cell_ioreason
gv$cell_config	gv$cell_ioreason_name
gv$cell_config_info	gv$cell_metric_desc
gv$cell_db	gv$cell_ofl_thread_history
gv$cell_disk	gv$cell_open_alerts
gv$cell_disk_history	gv$cell_request_totals
gv$cell_global	gv$cell_state
gv$cell_global_history	gv$cell_thread_history

Features and Feature Usage

gv$sql_feature	gv$sql_feature_hierarchy
gv$sql_feature_dependency

Fixed Views

gv$build_info	gv$indexed_fixed_column
gv$compatibility_requirement	gv$transportable_platform
gv$fixed_table	gv$version
gv$fixed_view_definition

SELECT * FROM gv$build_info;

      

       INST_ID BUILD_LABEL                         CON_ID

      -------- ---------------------------------- -------

             1 RDBMS_21.0.0.0.0_LINUX.X64_201030        0

SELECT * FROM gv$compatibility_requirement;

      

       INST_ID FEATURE                                        REQUIRED_COMPATIBI  CON_ID

      -------- ---------------------------------------------- ------------------ -------

             1 more than 64k sessions                         19.0.0.0.0               0

             1 PDB Flashback/PITR to orphan PDB incarnation   20.0.0.0.0               0

             1 inlined clob/blob column length up to 32KB     21.0.0.0.0               0

             1 subset database replication                    19.0.0.0.0               0

             1 hybrid partitioned tables                      19.0.0.0.0               0

             1 analytic view fact rows                        20.0.0.0.0               0

             1 derived analytic view                          18.0.0.0.0               0

             1 analytic view distinct join                    18.0.0.0.0               0

             1 auto analytic view cache                       18.0.0.0.0               0

             1 analytic view fact columns                     20.0.0.0.0               0

             1 attribute dimension cache star                 20.0.0.0.0               0

             1 derived analytic view base measures            20.0.0.0.0               0

             1 analytic view remote data sources              20.0.0.0.0               0

             1 analytic view count distinct                   20.0.0.0.0               0

             1 analytic view materialized aggregation tables  20.0.0.0.0               0

             1 analytic view base table transform             20.0.0.0.0               0

             1 analytic view base measure expressions         21.0.0.0.0               0

set linesize 102

      col banner format a71

      col banner_full format a71

      col banner_legacy format a71

      

      SELECT * FROM gv$version;

      

       INST_ID BANNER

      ---------- 
      -----------------------------------------------------------------------

      BANNER_FULL

      -----------------------------------------------------------------------

      BANNER_LEGACY CON_ID

      ----------------------------------------------------------------------- ----------

              1 Oracle Database 21c 
      Enterprise Edition Release 21.0.0.0.0 - Production

      Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

      Version 21.1.0.0.0

      Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production 0

Flashback Database

gv$flashback_database_log	v$flashback_txn_mods
gv$flashback_database_logfile	v$recovery_area_usage
gv$flashback_database_stat	gv$restore_point
v$flashback_txn_graph

Flashback Recovery Area

v$flash_recovery_area_usage

v$recovery_area_usage

Flashback Transaction

v$flashback_txn_graph

v$flashback_txn_mods

GoldenGate

gv$gg_apply_coordinator	gv$goldengate_capture
gv$gg_apply_reader	gv$goldengate_message_tracking
gv$gg_apply_receiver	gv$goldengate_procedure_stats
gv$gg_apply_server	v$goldengate_table_stats
gv$goldengate_capabilities	v$goldengate_transaction

Hang Detection

v$hang_info	gv$hang_statistics
v$hang_session_info

Health Management

v$diag_hm_fdg_set	gv$hm_info
v$diag_hm_finding	gv$hm_recommendation
v$diag_hm_info	gv$hm_run
v$diag_hm_message	gv$ir_failure
v$diag_hm_recommendation	gv$ir_failure_set
v$diag_hm_run	gv$ir_manual_checklist
gv$hm_check	gv$ir_repair
gv$hm_check_param	gv$session_fix_control
gv$hm_finding	gv$system_fix_control

In-Memory Database

gv$imeu_header	gv$im_segments_detail
gv$im_adoelements	gv$im_seg_ext_map
gv$im_adotaskdetails	gv$im_smu_chunk
gv$im_adotasks	gv$im_smu_delta
gv$im_column_level	gv$im_smu_head
gv$im_col_cu	gv$im_tbs_ext_map
gv$im_delta_header	gv$im_user_segments
gv$im_header	gv$inmemory_area
gv$im_globaldict	gvinmemory_faststart_area
gv$im_globaldict_piecemap	gv$inmemory_xmem_area
gv$im_globaldict_sortorder	gv$memoptimize_write_area
gv$im_globaldict_version	gv$scheduler_inmem_mdinfo
gv$im_imecol_cu	gv$scheduler_inmem_rtinfo
gv$im_segments

I/O

gv$filemetric	gv$iostat_function
gv$filemetric_history	gv$iostat_function_detail
gv$filestat	gv$iostat_network
gv$file_histogram	gv$io_calibration_status
gv$file_optimized_histogram	gv$io_outlier
gv$iofuncmetric	gv$kernel_io_outlier
gv$iofuncmetric_history	gv$lgwrio_outlier
gv$iostat_consumer_group	gv$tempstat
gv$iostat_file

CREATE TABLE avg_read_write

      (inst_id, avg_read_time, avg_write_time) AS

      SELECT inst_id, AVG(average_read_time), AVG(average_write_time)

      FROM gv$filemetric_history

      WHERE 0=1

      GROUP BY inst_id;

      

      CREATE OR REPLACE PROCEDURE file_io_metrics AUTHID DEFINER IS

      BEGIN

        INSERT INTO avg_read_write

        (inst_id, avg_read_time, avg_write_time)

        SELECT inst_id, AVG(average_read_time), AVG(average_write_time)

        FROM gv$filemetric_history

        WHERE average_read_time IS NOT NULL

        AND average_write_time IS NOT NULL

       
      GROUP BY inst_id

        ORDER BY 1;

      

        COMMIT;

      END file_io_metrics;

      /

      

      exec file_io_metrics

      

      -- sample output

      INST_ID    AVG(AVERAGE_READ_TIME) AVG(AVERAGE_WRITE_TIME)

      ---------- ---------------------- -----------------------

               5             .844172725              .027063522

               6             .542269401              .029147572

               7             .846074066              .186608746

               8            1.102195470              .071913735

               9             .519971963              .029391265

              10             .586967514              .071486951

Instance

gv$active_instances	gv$instance_ping
gv$instance

Java

gv$javapool	gv$java_pool_advice
gv$java_library_cache_memory	gv$java_services
gv$java_patching_status

Keywords

Reserved Words

Warning: This is an incomplete list of reserved words. desc gv$reserved_words set linesize 121 set pagesize 0 SELECT keyword FROM gv$reserved_words ORDER BY 1;

Latches & Locks

gv$dlm_all_locks	gv$latchholder
gv$dlm_convert_local	gv$latchname
gv$dlm_convert_remote	gv$latch_children
gv$dlm_latch	gv$latch_misses
gv$dlm_locks	gv$latch_parent
gv$dlm_misc	gv$libcache_locks
gv$dlm_ress	gv$lock
gv$dlm_traffic_controller	gv$locked_object
gv$enqueue_lock	gv$locks_with_collisions
gv$enqueue_stat	gv$lock_activity
gv$enqueue_statistics	gv$lock_element
gv$event_name	gv$lock_type
gv$file_ping	gv$mutex_sleep
gv$ges_blocking_enqueue	gv$mutex_sleep_history
gv$ges_deadlocks	gv$process
gv$ges_deadlock_sessions	gv$resource
gv$ges_enqueue	gv$resource_limit
gv$global_blocked_locks	gv$transaction_enqueue
gv$latch	gv_$_lock

Log Miner

gv$logmnr_contents	gv$logmnr_parameters
gv$logmnr_dictionary	gv$logmnr_process
gv$logmnr_dictionary_load	gv$logmnr_session
gv$logmnr_latch	gv$logmnr_stats
gv$logmnr_logfile	gv$logmnr_transaction
gv$logmnr_logs

Materialized View Refresh

gv$activity_mview	gv$mvrefresh
gv$activity_table

Memory Utilization

gv$memory_current_resize_ops	gv$sgastat
gv$memory_dynamic_components	gv$sga_current_resize_ops
gv$memory_resize_ops	gv$sga_dynamic_components
gv$memory_target_advice	gv$sga_dynamic_free_memory
gv$pgastat	gv$sga_resize_ops
gv$process_memory	gv$sga_target_advice
gv$sga	gv$shared_pool_advice
gv$sgainfo	gv$shared_pool_reserved

Metrics

gv$alert_types	gv$metricname
gv$eventmetric	gv$metric_history
gv$event_histogram	gv$sqlpa_metric
gv$event_histogram_micro	gv$sysmetric
gv$imhmseg	gv$sysmetric_history
gv$metric	gv$sysmetric_summary
gv$metricgroup	gv$threshold_types

Miscellaneous

vw_x$aud_xs_actions	v_$kxftask
gv$auto_bmr_statistics	gv$lcr_cache
gv$bcapply_stats	gv$license
gv$connection_broker	gv$listener_health
gv$consensus_clients	gv$listener_network
gv$consensus_logs	gv$mapped_sql
gv$consensus_stats	v$my_nologging_standby_txn
gv$lreg_updstat	gv$nonlogged_block
gv$pmem_filestore	gv$object_dml_frequencies
gv$authpool_stats	gv$online_redef
gv$bgprocess	gv$option
gv$blocking_quiesce	gv$patches
gv$bts_stat	gv$ping
gv$bt_scan_cache	gv$policy_history
gv$bt_scan_obj_temps	gv$quarantine
gv$calltag	gv$quarantine_summary
gv$code_clause	gv$resumable
gv$context	gv$scheduler_running_jobs
gv$corrupt_xid_list	gv$securefile_shrink
gv$database	gv$securefile_timer
gv$db_pipes	gv$sqlcommand
gv$dead_cleanup	gv$sqlfn_arg_metadata
gv$detached_session	gv$sqlfn_metadata
v$disk_restore_range	gv$sscr_sessions
gv$dual	gv$sysaux_occupants
gv$emx_usage_stats	gv$sys_report_requests
gv$exp_stats	gv$sys_report_stats
gv$false_ping (deprecated 12.1)	gv$sql_testcases
gv$flashfilestat	gv$timer
gv$gcr_actions	gv$timezone_file
gv$gcr_log	gv$timezone_names
gv$gcr_metrics	gv$toplevelcall
gv$gcr_status	gv$type_size
gv$heat_map_segment	v$unified_audit_record_format
gv$ios_client	gv$xs_sessions
gv$key_vector	gv$xs_session_ns_attributes
v_$kccdi	gv$xs_session_roles
v_$kccfe	gv$zonemap_usage_stats
v_x$kswsastab	gv$_lock

SELECT *

      FROM gv$type_size

      ORDER BY 3;

      

      SELECT clause_name, parameter_name

      FROM gv$code_clause

      ORDER BY 1,2;

      

      CLAUSE_NAME                    PARAMETER_NAME

      ------------------------------ ------------------------------

      ADG_REDIRECT_DML

      ADG_REDIRECT_PLSQL

      ADVISE

      AFFINITY

      ARCHIVE LOG                    ALL

      ARCHIVE LOG                    CHANGE

      ARCHIVE LOG                    CURRENT

      ARCHIVE LOG                    GROUP

      ARCHIVE LOG                    LOGFILE

      ARCHIVE LOG                    NEXT

      ARCHIVE LOG                    SEQUENCE

      CHECK DATAFILES

      CHECKPOINT

      CLOSE DATABASE LINK

      COMMIT IN PROCEDURE

      COPY LOGFILE

      DISTRIBUTED RECOVERY

      DUMP                           DATAFILE

      DUMP                           FLASHBACK

      DUMP                           LOGFILE

      DUMP                           REDO

      DUMP                           TEMPFILE

      DUMP                           UNDO

      END                            SESSION DISCONNECT SESSION

      END                            SESSION KILL SESSION

      EXCLUSIVE INSTANCE

      FLUSH                          BUFFER_CACHE

      FLUSH                          FLASH_CACHE

      FLUSH                          GLOBAL CONTEXT

      FLUSH                          REDO

      FLUSH                          SHARED_POOL

      GUARD

      ILM ROW                        ACCESS TRACKING

      ILM ROW                        MODIFICATION TRACKING

      LOGICAL REPLICATION

      PARALLEL                       DDL

      PARALLEL                       DML

      PARALLEL                       QUERY

      QUIESCE                        QUIESCE RESTRICTED

      QUIESCE                        UNQUIESCE

      REFRESH LDAP_REGISTRATION

      REGISTER

      RESET

      RESUMABLE

      RESUME

      SECURITY                       RESTRICTED SESSION

      SECURITY                       SET ENCRYPTION KEY

      SECURITY                       SET ENCRYPTION WALLET xx

      SET                            APPLICATION ACTION

      SET                            APPLICATION MODULE

      SET                            CONSTRAINTS

      SET                            CONTAINER

      SET                            CURRENT_SCHEMA

      SET                            EDITION

      SET                            ERROR_ON_OVERLAP_TIME

      SET                            EVENTS

      SET                            EVENTS

      SET                            FLAGGER

      SET                            GLOBAL_TOPIC_ENABLED

      SET                            ILM VISIBILITY

      SET                            INSTANCE

      SET                            ISOLATION_LEVEL

      SET                            LDAP_REGISTRATION_ENABLED

      SET                            LDAP_REG_SYNC_INTERVAL

      SET                            LOG_READ_ONLY_VIOLATIONS

      SET                            SINGLETASK DEBUG

      SET                            SQL_TRANSLATION_PROFILE

      SET                            STANDBY_MAX_DATA_DELAY

      SET                            TIME_ZONE

      SET                            USE_PRIVATE_OUTLINES

      SET                            USE_STORED_OUTLINES

      SET                            USE_STORED_OUTLINES

      SET

      SET

      SHARD DDL

      SHARD OPERATIONS

      SHUTDOWN

      SUSPEND

      SWITCH LOGFILE

      SYNC WITH PRIMARY

      TX RECOVERY

OLAP

gv$aw_aggregate_op	gv$aw_longops
gv$aw_allocate_op	gv$aw_olap
gv$aw_calc	gv$aw_session_info

Optimizer

gv$column_statistics	gv$ses_optimizer_env
gv$index_usage_info	gv$sql_hint
gv$optimizer_processing_rate	gv$sql_optimizer_env
gv$query_block_origin	gv$sql_reoptimization_hints
gv$rt_addm_control	gv$sys_optimizer_env

Oracle File System (OFS)

gv$ofsmount

gv$ofs_stats

Parallel Query

gv$execution	gv$px_instance_group
gv$parallel_degree_limit_mth	gv$px_process
gv$pq_sesstat	gv$px_process_sysstat
gv$pq_slave	gv$px_process_trace
gv$pq_slaveinfo	gv$px_session
gv$pq_sysstat	gv$px_sesstat
gv$pq_tqstat	gv$qpx_inventory
gv$px_buffer_advice

Parameters

Session specific information for connected sessions.

This query shows the values that are non-default and deprecated. These will most likely trigger a warning message during startup.

gv$nls_parameters	gv$spparameter
gv$nls_valid_values	gv$system_parameter
gv$obsolete_parameter	gv$system_parameter2
gv$parameter	gv$system_reset_parameter
gv$parameter2	gv$system_reset_parameter2
gv$parameter_valid_values

desc gv$parameter

      

      set linesize 121

      set pagesize 25

      col name format a35

      col value format a70

      

      SELECT name, value

      FROM gv$parameter

      WHERE isdefault = 'FALSE'

      AND isdeprecated = 'TRUE'

      ORDER BY 1;

Processes

gv$cleanup_process	gv$process_memory_detail_prog
gv$process_group	gv$process_pool
gv$process_memory_detail	gv$process_priority_data

Real Application Clusters (RAC)

gv$active_instances	gv$gc_element
gv$cluster_interconnects	gv_$gc_elements_w_collisions
gv$configured_interconnects	gv$gwm_rac_affinity (leg)
gv$dynamic_remaster_stats	gv$instance_cache_transfer
gv$file_cache_transfer	gv$temp_cache_transfer

Redo Logs

gv$instance_log_group	gv$log_history
gv$log	gv$redo_dest_resp_histogram
gv$logfile	v$rollname
gv$loghist	gv$thread

Resource Usage

Resource limits and resource utilization

gv$access	gv$rsrc_cons_group_history
gv$iostat_consumer_group	gv$rsrc_pdb
gv$rsrcmgrmetric	gv$rsrc_pdb_history
gv$rsrcmgrmetric_history	gv$rsrc_plan
gv$rsrcpdbmetric	gv$rsrc_plan_cpu_mth
gv$rsrcpdbmetric_history	gv$rsrc_plan_history
gv$rsrc_consumer_group	gv$rsrc_session_info
gv$rsrc_consumer_group_cpu_mth

desc gv$resource_limit

      

      col resource_name format a24

      col initial_allocation format a10

      col limit_value format a10

      

      SELECT * FROM gv$resource_limit ORDER BY 2;

      

      INST_ID RESOURCE_NAME  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU CON_ID

      ------- -------------- ------------------- --------------- ---------- ---------- ------

            1 branches                         0               0        554 UNLIMITED       1

            1 cmtcallbk                        0               3        554 UNLIMITED       1

            1 dml_locks                        0              26       2216 UNLIMITED       1

            1 enqueue_locks                   27              49       6060      6060       1

            1 enqueue_resources               22              57       2436 UNLIMITED       1

            1 gcs_resources                    0               0          0 UNLIMITED       1

            1 gcs_shadows                      0               0          0 UNLIMITED       1

            1 ges_big_msgs                     0               0          0 UNLIMITED       1

            1 ges_cache_ress                   0               0          0 UNLIMITED       1

            1 ges_locks                        0               0          0 UNLIMITED       1

            1 ges_procs                        0               0          0         0       1

            1 ges_reg_msgs                     0               0          0 UNLIMITED       1

            1 ges_ress                         0               0          0 UNLIMITED       1

            1 ges_rsv_msgs                     0               0          0         0       1

            1 k2q_locks                        0               0       1008 UNLIMITED       1

            1 max_rollback_segments           11              11        554     65535       1

            1 max_shared_servers               1               2  UNLIMITED UNLIMITED       1

            1 parallel_max_servers             8               8         80     32767       1

            1 processes                       63              83        320       320       1

            1 sessions                        78             115        504       504       1

            1 smartio_buffer_memory            0               0          0 UNLIMITED       1

            1 smartio_metadata_memory          0               0          0 UNLIMITED       1

            1 smartio_overhead_memory          0          168984          0 UNLIMITED       1

            1 smartio_sessions                 0               1          0 UNLIMITED       1

            1 sort_segment_locks               0               8  UNLIMITED UNLIMITED       1

            1 temporary_table_locks            0               5  UNLIMITED UNLIMITED       1

            1 transactions                     0               6        554 UNLIMITED       1

Result Cache

gv$client_result_cache_stats	gv$result_cache_objects
gv$result_cache_dependency	gv$result_cache_statistics
gv$result_cache_memory	gv$result_subcache_statistics

Rollback Segments / Undo Segments

v$rollname	gv$undostat
gv$rollstat

Rules

gv$rule	gv$rule_set_aggregate_stats
gv$rule_set

Security, Privileges, Data Masking/Redaction

gv$client_secrets	v$object_privilege
gv$context	gv$passwordfile_info
gv$database_key_info	gv$pkcs11_path
gv$enabledprivs	gv$pwfile_users
gv$encrypted_tablespaces	gv$ro_user_account
gv$encryption_keys	gv$tsdp_supported_feature
gv$encryption_wallet	gv$unified_audit_trail
gv$exadirect_acl	gv$vpd_policy
gv$globalcontext	gv$wallet
gv$ip_acl	gv$xml_audit_trail
gv$lockdown_rules

SELECT * FROM gv$passwordfile_info; INST_ID FILE_NAME FORMAT IS_AS IS_EX CON_ID --------- ---------------------------------- ------ ----- ----- ------ 1 /u01/app/oracle/dbs/orapwtest21db 12 FALSE FALSE 0

Services

gv$active_services	gv$service_event
gv$servicemetric	gv$service_region_metric
gv$servicemetric_history	gv$service_stats
gv$services	gv$service_wait_class

Sessions

Session specific information for connected sessions

gv$active_session_history	gv$session_fix_control
gv$active_sess_pool_mth	gv$session_longops
gv$client_stats	gv$session_object_cache
gv$mystat	gv$session_wait
gv$session	gv$session_wait_class
v$sessions_count	gv$session_wait_history
gv$session_blockers	gv$sessmetric
gv$session_connect_info	gv$sesstat
gv$session_cursor_cache	gv$sess_io
gv$session_event	gv$sess_time_model

desc gv$session

      

      col username format a10

      col schemaname format a10

      col osuser format a25

      col machine format a20

      col service_name format a25

      col program format a25

      col terminal format a10

      

      -- audsid: audit SID

      SELECT sid, audsid, username, status, server, schemaname, osuser, type, service_name

      FROM gv$session;

      

      SELECT username, command, machine, terminal, program, state

      FROM gv$session;

Shared Servers (formerly MTS)

gv$circuit	gv$reqdist
gv$dispatcher	gv$shared_server
gv$dispatcher_config	gv$shared_server_monitor
gv$dispatcher_rate	gv$shared_server_stat
gv$max_active_sess_target_mth

Storage

gv$chunk_metric	gv$nfs_locks
gv$datafile	gv$nfs_open_files
gv$datafile_header	gv$offline_range
gv$dbfile	gv$segspace_usage
gv$filespace_usage	gv$tablespace
gv$lobstat	gv$temporary_lobs
gv$nfs_clients

Streams

gv$streams_apply_coordinator	gv$streams_message_tracking
gv$streams_apply_reader	gv$streams_pool_advice
gv$streams_apply_server	gv$streams_pool_statistics
gv$streams_capture	gv$streams_transaction

System Statistics

gv$dml_stats	gv$statistics_level
gv$global_transaction	gv$statname
gv$osstat	gv$sysstat
gv$segment_statistics	gv$system_cursor_cache
gv$segstat	gv$system_event
gv$segstat_name	gv$sys_time_model
gv$serv_mod_act_stats	gv$transaction
gv$sort_usage

Temp Space

gv$sort_segment	gv$tempfile
gv$tempfile_info_instance	gv$tempseg_usage
gv$temp_extent_map	gv$tempstat
gv$temp_extent_pool	gv$tempundostat
gv$temp_space_header

Transportable Platforms

gv$transportable_platform

SELECT * FROM gv$transportable_platform ORDER BY 2; INST_ID PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT CON_ID -------- ----------- ---------------------------------- -------------- ------- 1 1 Solaris[tm] OE (32-bit) Big 0 1 2 Solaris[tm] OE (64-bit) Big 0 1 3 HP-UX (64-bit) Big 0 1 4 HP-UX IA (64-bit) Big 0 1 5 HP Tru64 UNIX Little 0 1 6 AIX-Based Systems (64-bit) Big 0 1 7 Microsoft Windows IA (32-bit) Little 0 1 8 Microsoft Windows IA (64-bit) Little 0 1 9 IBM zSeries Based Linux Big 0 1 10 Linux IA (32-bit) Little 0 1 11 Linux IA (64-bit) Little 0 1 12 Microsoft Windows x86 64-bit Little 0 1 13 Linux x86 64-bit Little 0 1 15 HP Open VMS Little 0 1 16 Apple Mac OS Big 0 1 17 Solaris Operating System (x86) Little 0 1 18 IBM Power Based Linux Big 0 1 19 HP IA Open VMS Little 0 1 20 Solaris Operating System (x86-64) Little 0 1 21 Apple Mac OS (x86-64) Little 0 1 22 Linux OS (S64) Big 0

Waits

gv$channel_waits	gv$session_wait_history
gv$event_histogram_micro	gv$system_wait_class
gv$event_outliers	gv$waitclassmetric
gv$service_wait_class	gv$waitclassmetric_history
gv$session_blockers	gv$waitstat
gv$session_wait	v$wait_chains
gv$session_wait_class

Workload Management

gv$wlm_db_mode	gv$wlm_pcservice
gv$wlm_pcmetric	gv$wlm_pc_stats
gv$wlm_pcmetric_history

Workload Replay

gv$database_replay_progress	gv$replay_context_sysdate
gv$replaycontext_systimestamp	gv$replay_context_systimestamp
gv$replay_context	gv$replay_context_sysguid
gv$replay_context_lob	gv$workload_replay_thread
gv$replay_context_sequence

XStreams

Heterogeneous Streams

gv$xstream_apply_coordinator	gv$xstream_message_tracking
v$xstream_apply_reader	gv$xstream_outbound_server
v$xstream_apply_receiver	v$xstream_table_stats
v$xstream_apply_server	gv$xstream_transaction
gv$xstream_capture