CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Dynamic Performance Views
Version 11.1
 
General Information
Note: 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$ whenever possible.

Categories
Quick-Link Contents
Advanced Queuing (AQ) Fixed Views RAC
Advisors Flashback Database Redo Logs
Archive Logging Flashback Recovery Area Resource Usage
ASH Flashback Transaction Result Cache
ASM Health Management Rollback/Undo Segments
Backup & Restore I/O Rules
Buffers Instance Security & Privileges
Caches Keywords Services
Connection Pool Latches & Locks Sessions
Control File Log Miner Shared Server
Cursors & SQL Statements Materialized View Replication Storage
Data Guard & Standbys Memory Utilization Streams
Database Links Metrics System Statistics
Data Pump Miscellaneous Temp Space
Diagnostics OLAP Waits
Direct Loader Optimizer Workload Management
Exadata Parameters Jobs
Feature Usage Parallel Query  
Query to identify V$ views not duplicated as GV$ views set linesize 121

SELECT object_name
FROM dba_objects
WHERE object_name LIKE 'V$%'
AND object_name NOT IN (
  SELECT SUBSTR(object_name, 2)
  FROM dba_objects
  WHERE object_name LIKE 'GV$%')
ORDER BY 1;
 
Advanced Queuing
 

gv$aq

gv$buffered_subscribers
gv$aq1 gv$queue

gv$buffered_publishers

gv$queueing_mth
gv$buffered_queues  
 
Advisors
 

gv$advisor_progress

gv$pga_target_advice
gv$java_pool_advice gv$pga_target_advice_histogram
gv$mttr_target_advice gv$px_buffer_advice
 
Archive Logging
 

gv$archive

v$proxy_archivelog_details

gv$archive_dest

v$proxy_archivelog_summary
gv$archive_dest_status  

gv$archive_gap

 
gv$archive_processes  
gv$archived_log  
gv$proxy_archivedlog  
 
ASH
 

gv$active_session_history

 
 
ASM
 

gv$asm_alias

gv$asm_disk_iostat gv$asm_file
gv$asm_attribute gv$asm_disk_stat gv$asm_operation

gv$asm_client

gv$asm_diskgroup gv$asm_template

gv$asm_disk

gv$asm_diskgroup_stat  
 
Backup and Recovery (RMAN)
 
gv$backup v$backup_archivelog_details

gv$backup_async_io

v$backup_archivelog_summary
gv$backup_corruption v$backup_controlfile_details
gv$backup_datafile v$backup_controlfile_summary
gv$backup_device v$backup_copy_details
gv$backup_piece v$backup_copy_summary
gv$backup_redolog v$backup_datafile_details
gv$backup_set v$backup_datafile_summary

gv$backup_spfile

v$backup_files
gv$backup_sync_io v$backup_piece_details
gv$copy_corruption v$backup_set_details
gv$database_block_corruption v$backup_set_summary
gv$database_incarnation v$backup_spfile_details
gv$datafile_copy v$backup_spfile_summary
gv$db_transportable_platform v$block_change_tracking
gv$deleted_object v$proxy_copy_details
gv$fast_start_servers v$proxy_copy_summary

gv$fast_start_transactions

v$recovery_file_dest

gv$instance_recovery

v$rman_backup_job_details
gv$proxy_datafile v$rman_backup_subjob_details
gv$recover_file v$rman_backup_type
gv$recovery_file_status v$rman_encryption_algorithms

gv$recovery_log

v$rman_status

gv$recovery_progress

v$unusable_backupfile_details
gv$recovery_status  
gv$rman_compression_algorithm  
gv$rman_configuration  

gv$rman_encryption_algorithms

 

gv$rman_output

 
 
Buffers
 

gv$bh

gv$buffer_pool gv$buffer_pool_statistics
 
Caches

bsp = block server background process

gv$bsp

gv$map_element

gv$class_ping

gv$map_ext_element

gv$cr_block_server

gv$map_file

gv$current_block_server

gv$map_file_extent

gv$db_cache_advice

gv$map_file_io_stack

gv$db_object_cache

gv$map_library

gv$file_cache_transfer

gv$map_subelement

gv$gcshvmaster_info

gv$propagation_receiver

gv$gcspfmaster_info

gv$propagation_sender

gv$hvmaster_info

gv$rowcache

gv$javapool

gv$rowcache_parent

gv$java_library_cache_memory

gv$rowcache_subordinate

gv$library_cache_memory

gv$subcache

gv$librarycache

gv$temp_ping

gv$map_comp_list

 
 
Connection Pool
 

gv$cpool_cc_info

gv$cpool_cc_stats gv$cpool_stats
 
Control Files
 

gv$controlfile

gv$controlfile_record_section
 
Cursors and SQL Statements
 

gv$object_dependency

gv$sql_redirection
gv$open_cursor gv$sql_shared_cursor

gv$sql

gv$sql_shared_memory

gv$sql_bind_capture

gv$sql_workarea
gv$sql_bind_data gv$sql_workarea_active
gv$sql_bind_metadata gv$sql_workarea_histogram

gv$sql_cursor

gv$sqlarea

gv$sql_join_filter

gv$sqlarea_plan_hash

gv$sql_plan

gv$sqlstats

gv$sql_plan_statistics

gv$sqltext

gv$sql_plan_statistics_all

gv$sqltext_with_newlines
 
Data Guard

See also Standby Databases

gv$dataguard_config

gv$logstdby_progress

gv$dataguard_stats

gv$logstdby_state
v$dataguard_status gv$logstdby_stats
gv$fs_failover_histogram gv$logstdby_transaction
gv$fs_failover_stats gv$managed_standby
gv$foreign_archived_log gv$rfs_thread
gv$logstdby gv$standby_apply_snapshot
gv$logstdby_process gv$standby_log
 
Database Links & Heterogeneous Services
 
gv$dblink gv$hs_parameter

gv$hs_agent

gv$hs_session
 
Data Pump
 

gv$datapump_job

gv$datapump_session
 
Diagnostics
 
gv$diag_info v$diag_adr_control
  v$diag_adr_invalidation
  v$diag_alert_ext
  v$diag_ams_xaction
  v$diag_critical_error
  v$diag_dde_user_action
  v$diag_dde_user_action_def
  v$diag_dde_usr_act_param
  v$diag_dde_usr_act_param_def
  v$diag_dde_usr_inc_act_map
  v$diag_dde_usr_inc_type
  v$diag_diagv_incident
  v$diag_dir_ext
  v$diag_em_diag_job
  v$diag_em_target_info
  v$diag_em_user_activity
  v$diag_inc_meter_config
  v$diag_inc_meter_impt_def
  v$diag_inc_meter_info
  v$diag_inc_meter_pk_impts
  v$diag_inc_meter_summary
  v$diag_incckey
  v$diag_incident
  v$diag_incident_file
  v$diag_ips_configuration
  v$diag_ips_file_copy_log
  v$diag_ips_file_metadata
  v$diag_ips_package
  v$diag_ips_package_file
  v$diag_ips_package_history
  v$diag_ips_package_incident
  v$diag_ips_pkg_unpack_hist
  v$diag_ips_remote_package
  v$diag_pickleerr
  v$diag_problem
  v$diag_relmd_ext
  v$diag_sweeperr
  v$diag_v_actinc
  v$diag_v_actprob
  v$diag_v_inc_meter_info_prob
  v_$diag_v_inccount
  v_$diag_v_incfcount
  v_$diag_v_ipsprbcnt
  v_$diag_v_ipsprbcnt1
  v_$diag_v_nfcinc
  v_$diag_v_swperrcount
  v_$diag_vem_user_actlog
  v_$diag_vem_user_actlog1
  v_$diag_view
  v_$diag_viewcol
  v_$diag_vinc_meter_info
  v_$diag_vincident
  v_$diag_vincident_file
  v_$diag_vips_file_copy_log
  v_$diag_vips_file_metadata
  v_$diag_vips_package_file
  v_$diag_vips_package_history
  v_$diag_vips_package_main_int
  v_$diag_vips_package_size
  v_$diag_vips_pkg_file
  v_$diag_vips_pkg_inc_cand
  v_$diag_vips_pkg_inc_dtl
  v_$diag_vips_pkg_inc_dtl1
  v_$diag_vips_pkg_main_problem
  v_$diag_vnot_exist_incident
  v_$diag_vproblem
  v_$diag_vproblem1
  v_$diag_vproblem2
  v_$diag_vproblem_bucket
  v_$diag_vproblem_bucket1
  v_$diag_vproblem_bucket_count
  v_$diag_vproblem_int
  v_$diag_vproblem_lastinc
  v_$diag_vshowcatview
  v_$diag_vshowincb
  v_$diag_vshowincb_i
  v_$diag_vtest_exists
 
Direct Loader
 

gv$loadistat

gv$loadpstat
 
Exadata
 
gv$cell gv$cell_state
gv$cell_config gv$cell_thread_history
gv$cell_request_totals v$sqlfn_metadata.offloadable
 
 
Feature Usage
 

gv$sql_feature

gv$sql_feature_hierarchy

gv$sql_feature_dependency

 
 
Fixed Views
 

gv$fixed_table

gv$transportable_platform

gv$fixed_view_definition

gv$version
gv$indexed_fixed_column  
 
Flashback Database
 

gv$flashback_database_log

v$flash_recovery_area_usage

gv$flashback_database_logfile

v$flashback_txn_graph
gv$flashback_database_stat v$flashback_txn_mods
gv$restore_point  
 
Health Management
 

gv$hm_check

gv$hm_run
gv$hm_check_param gv$ir_failure
gv$hm_finding gv$ir_failure_set
gv$hm_info gv$ir_manual_checklist
gv$hm_recommendation gv$ir_repair
 
I/O
 

gv$file_histogram

gv$iofuncmetric_history
gv$filemetric gv$iostat_consumer_group
gv$filemetric_history gv$iostat_file
gv$filestat gv$iostat_function
gv$tempstat gv$iostat_network
gv$iofuncmetric gv$io_calibration_status
 
Instance
 

gv$active_instances

gv$instance
 
Jobs
 

gv$scheduler_running_jobs

 
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$class_cache_transfer gv$latchholder

gv$dlm_all_locks

gv$latchname
gv$dlm_convert_local gv$latch_children
gv$dlm_convert_remote gv$latch_misses
gv$dlm_latch gv$latch_parent
gv$dlm_locks gv$lock
gv$dlm_misc gv$lock_activity
gv$dlm_ress gv$lock_element
gv$dlm_traffic_controller gv$lock_type
gv$enqueue_lock gv$locked_object
gv$enqueue_stat gv$locks_with _collisions
gv$enqueue_statistics gv$mutex_sleep
gv$event_name gv$mutex_sleep_history
gv$file_ping gv$process
gv$ges_blocking_enqueue gv$resource
gv$ges_enqueue gv$resource_limit
gv$global_blocked_locks gv$transaction_enqueue
gv$latch  
 
Log Miner
 

gv$logmnr_callback

gv$logmnr_parameters
gv$logmnr_contents gv$logmnr_process
gv$logmnr_dictionary gv$logmnr_region
gv$logmnr_dictionary_load gv$logmnr_session
gv$logmnr_latch gv$logmnr_stats
gv$logmnr_logfile gv$logmnr_transaction
gv$logmnr_logs  
 
Materialized View Replication
 

gv$mvrefresh

gv$replprop gv$replqueue
 
Memory Utilization
 

gv$pgastat

gv$sga_resize_ops
gv$process_memory gv$sgainfo

gv$sga

gv$sgastat
gv$sga_current_resize_ops gv$shared_pool_advice
gv$sga_dynamic_components gv$shared_pool_reserved
gv$sga_dynamic_free_memory  
 
Metrics
 

gv$alert_types

gv$metricgroup gv$sysmetric_history
gv$event_histogram gv$metricname gv$sysmetric_summary
gv$eventmetric gv$metric_history gv$threshold_types

gv$metric

gv$sysmetric  
 
Miscellaneous
 

gv$bgprocess

gv$license gv$timer
gv$blocking_quiesce v$object_usage gv$timezone_file
gv$context gv$option gv$timezone_names
gv$database gv$resumable gv$type_size
gv$db_pipes gv$sysaux_occupants  
SELECT *
FROM gv$type_size
ORDER BY 3;
 
OLAP
 

gv$aw_aggregate_op

gv$aw_calc gv$aw_olap
gv$aw_allocate_op gv$aw_longops gv$aw_session_info
 
Optimizer
 

gv$ses_optimizer_env

gv$sql_optimizer_env gv$sys_optimizer_env
 
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$parameter2
gv$nls_valid_values gv$spparameter
gv$obsolete_parameter gv$system_parameter
gv$parameter gv$system_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;
 
Parallel Query
 

gv$execution

gv$px_buffer_advice
gv$parallel_degree_limit_mth gv$px_instance_group
gv$pq_sesstat gv$px_process
gv$pq_slave gv$px_process_sysstat
gv$pq_sysstat gv$px_session
gv$pq_tqstat gv$px_sesstat
 
Real Application Clusters (RAC)
 

gv$active_instances

gv$gc_elements_with_collisions

gv$cluster_interconnects

gv$file_cache_transfer
gv$configured_interconnects gv$instance_cache_transfer
gv$gc_element gv$temp_cache_transfer
 
Redo Logs
 

gv$instance_log_group

gv$loghist v$rollname

gv$log

gv$log_history  
gv$logfile gv$thread  
 
Resource Usage

Resource limits and resource utilization
gv$access gv$rsrc_consumer_group_cpu_mth
gv$iostat_consumer_group gv$rsrc_plan
gv$rsrcmgrmetric gv$rsrc_plan_cpu_mth
gv$rsrcmgrmetric_history gv$rsrc_plan_history
gv$rsrc_consume_group_cpu_mth gv$rsrc_session_info
gv$rsrc_consumer_group v$rsrc_consumer_group_cpu_mth
gv$rsrc_cons_group_history  
desc gv$resource_limit

set linesize 121

SELECT * FROM gv$resource_limit;
 
Result Cache
 

gv$client_result_cache_stats

gv$result_cache_objects
gv$result_cache_dependency gv$result_cache_statistics
gv$result_cache_memory  
 
Rollback Segments / Undo Segments
 

v$rollname

gv$rollstat gv$undostat
 
Rules
 

gv$rule

gv$rule_set gv$rule_set_aggregate_stats
 
Security and Privileges
 

gv$context

gv$encryption_wallet gv$vpd_policy

gv$enabledprivs

gv$globalcontext gv$wallet
gv$encrypted_tablespaces gv$pwfile_users gv$xml_audit_trail
 
Services
 
gv$active_services gv$servicemetric
gv$service_event gv$servicemetric_history
gv$service_stats gv$services
 
Sessions

Session specific information for connected sessions
gv$active_sess_pool_mth gv$session_event
gv$active_session_history gv$session_longops
gv$client_stats gv$session_object_cache
gv$mystat gv$session_wait
gv$sess_io gv$session_wait_class
gv$sess_time_model gv$session_wait_history
gv$session gv$sessmetric
gv$session_connect_info gv$sesstat
gv$session_cursor_cache  
desc gv$session

set linesize 121
set pagesize 25
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$max_active_sess_target_mth

gv$dispatcher

gv$reqdist

gv$dispatcher_config

gv$shared_server

gv$dispatcher_rate

gv$shared_server_monitor
 
Standby Database
 
gv$logstdby gv$logstdby_state gv$managed_standby
gv$logstdby_process gv$logstdby_stats gv$standby_apply_snapshot
gv$logstdby_progress gv$logstdby_transaction gv$standby_log
 
Storage
 

gv$datafile

gv$nfs_locks
gv$datafile_header gv$nfs_open_files
gv$dbfile gv$offline_range
gv$filespace_usage gv$tablespace
gv$lobstat gv$temporary_lobs
gv4nfs_client  
 
Streams
 

gv$streams_apply_coordinator

gv$streams_message_tracking
gv$streams_apply_reader gv$streams_pool_advice
gv$streams_apply_server gv$streams_transaction
gv$streams_capture  
 
System Statistics
 

gv$global_transaction

gv$statistics_level

gv$osstat

gv$statname

gv$segment_statistics

gv$sys_time_model

gv$segstat

gv$sysstat

gv$segstat_name

gv$system_cursor_cache

gv$serv_mod_act_stats

gv$system_event
gv$sort_usage gv$transaction
 
Temp Space
 
gv$sort_segment gv$tempfile

gv$temp_extent_map

gv$tempseg_usage

gv$temp_extent_pool

gv$tempstat
gv$temp_space_header  
 
Waits
 
gv$service_wait_class gv$waitclassmetric
gv$session_wait gv$waitclassmetric_history
gv$session_wait_class gv$waitstat
gv$session_wait_history v$wait_chains

gv$system_wait_class

 
 
Workload Management
 
gv$wlm_pc_stats gv_$wlm_pcmetric gv_$wlm_pcmetric_history
 
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [239 users online]    © 2010 psoug.org