Sunday, April 18, 2010

All About V$ views

This can help a lot to an Oracle DBA to get info about different views:

Advisors: Information related to cache advisors

V$PGA_TARGET_ADVICE
V$PGA_TARGET_ADVICE_HISTOGRAM
V$MTTR_TARGET_ADVICE
V$PX_BUFFER_ADVICE
V$DB_CACHE_ADVICE
V$SHARED_POOL_ADVICE
V$JAVA_POOL_ADVICE
V$STREAMS_POOL_ADVICE (10.2)
V$SGA_TARGET_ADVICE (10.2)
V$ADVISOR_PROGRESS (10.2)

ASM

V$ASM_ALIAS (10.1)
V$ASM_CLIENT(10.1)
V$ASM_DISK(10.1)
V$ASM_DISK_STAT(10.2)
V$ASM_DISKGROUP(10.1)
V$ASM_DISKGROUP_STAT(10.2)
V$ASM_FILE(10.1)
V$ASM_OPERATION(10.1)
V$ASM_TEMPLATE(10.1)

Backup/recovery Information related to database backups and recovery including last backup,archive logs,state of files for backup,and recovery

V$ARCHIVE
V$ARCHIVED_LOG
V$ARCHIVE_DEST
V$ARCHIVE_DEST_STATUS
V$ARCHIVE_GAP
V$ARCHIVE_PROCESSES
V$BACKUP
V$BACKUP_ASYNC_IO
V$BACKUP_CORRUPTION
V$BACKUP_DATAFILE
V$BACKUP_DEVICE
V$BACKUP_PIECE
V$BACKUP_REDOLOG
V$BACKUP_SET
V$BACKUP_SYNC_IO
V$BLOCK_CHANGE_TRACKING
V$COPY_CORRUPTION
V$DATABASE_BLOCK_CORRUPTION
V$DATABASE_INCARNATION
V$DATAFILE_COPY
V$DELETED_OBJECT
V$FAST_START_SERVERS
V$FAST_START_TRANSACTIONS
V$INSTANCE_RECOVERY
V$MTTR_TARGET_ADVICE
V$PROXY_ARCHIVEDLOG
V$PROXY_DATAFILE
V$RMAN_CONFIGURATION
V$RECOVERY_FILE_STATUS
V$RECOVERY_LOG
V$RECOVERY_PROGRESS
V$RECOVERY_STATUS
V$RECOVER_FILE
V$BACKUP_ARCHIVELOG_DETAILS(10.2)
V$BACKUP_ARCHIVELOG_SUMMARY(10.2)
V$BACKUP_CONTROLFILE_DETAILS(10.2)
V$BACKUP_CONTROLFILE_SUMMARY(10.2)
V$BACKUP_COPY_DETAILS(10.2)
V$BACKUP_COPY_SUMMARY(10.2)
V$BACKUP_FILES(10.1)
V$BACKUP_PIECE_DETAILS(10.2)
V$BACKUP_SET_DETAILS(10.2)
V$BACKUP_SET_SUMMARY(10.2)
V$BACKUP_SPFILE
V$BACKUP_SPFILE_DETAILS(10.2)
V$BACKUP_SPFILE_SUMMARY(10.2)
V$DATAFILE_HEADER
V$FLASH_RECOVERY_AREA_USAGE(10.2)
V$FLASHBACK_DATABASE_LOG(10.1)
V$FLASHBACK_DATABASE_STAT(10.1)
V$OBSOLETE_BACKUP_FILES
V$OFFLINE_RANGE
V$PROXY_ARCHIVELOG_DETAILS(10.2)
V$PROXY_ARCHIVELOG_SUMMARY(10.2)
V$PROXY_COPY_DETAILS(10.2)
V$PROXY_COPY_SUMMARY(10.2)
V$RECOVERY_FILE_DEST(10.1)
V$RESTORE_POINT(10.2)
V$RMAN_BACKUP_JOB_DETAILS(10.2)
V$RMAN_BACKUP_SUBJOB_DETAILS(10.2)
V$RMAN_BACKUP_TYPE(10.2)
V$RMAN_ENCRYPTION_ALGORITHMS(10.2)
V$RMAN_OUTPUT(10.1)
V$RMAN_STATUS(10.1)
V$UNUSABLE_BACKUPFILE_DETAILS(10.2)

Caches Information related to the various caches, including objects, library, cursors, and the dictionary

V$ACCESS
V$BUFFER_POOL
V$BUFFER_POOL_STATISTICS
V$DB_CACHE_ADVICE
V$DB_OBJECT_CACHE
V$JAVA_POOL_ADVICE
V$LIBRARYCACHE
V$LIBRARY_CACHE_MEMORY
V$PGASTAT
V$PGA_TARGET_ADVICE
V$PGA_TARGET_ADVICE_HISTOGRAM
V$ROWCACHE
V$ROWCACHE_PARENT
V$ROWCACHE_SUBORDINATE
V$SESSION_CURSOR_CACHE
V$SGA
V$SGASTAT
V$SGA_CURRENT_RESIZE_OPS
V$SGA_DYNAMIC_COMPONENTS
V$SGA_DYNAMIC_FREE_MEMORY
V$SGA_RESIZE_OPS
V$SGAINFO
V$SHARED_POOL_ADVICE
V$SHARED_POOL_RESERVED
V$SYSTEM_CURSOR_CACHE
V$SUBCACHE
V$JAVA_LIBRARY_CACHE_MEMORY(10.1)
V$PROCESS_MEMORY(10.2)
V$SGA_TARGET_ADVICE(10.2)

Cache Fusion/RAC

V$ACTIVE_INSTANCES
V$BH
V$CLUSTER_INTERCONNECTS(10.2)
V$CONFIGURED_INTERCONNECTS(10.2)
V$CR_BLOCK_SERVER
V$CURRENT_BLOCK_SERVER(10.1)
V$GC_ELEMENT
VGCSHVMASTER_INFO
V$GCSPFMASTER_INFO
V$GES_BLOCKING_ENQUEUE
V$GES_CONVERT_LOCAL
V$GES_CONVERT_REMOTE
V$GES_ENQUEUE
V$GES_LATCH
V$GES_RESOURCE
V$GES_STATISTICS
V$HVMASTER_INFO
V$INSTANCE_CACHE_TRANSFER
V$RESOURCE_LIMIT

Control files Information related to instance control files

V$CONTROLFILE
V$CONTROLFILE_RECORD_SECTION

Cursors/SQL statements Information related to cursors and SQL statements, including the open cursors, statistics, and actual SQL text

V$OPEN_CURSOR
V$SQL
V$SQLAREA
V$SQLTEXT
V$SQLTEXT_WITH_NEWLINES
V$SQL_BIND_DATA
V$SQL_BIND_METADATA
V$SQL_CURSOR
V$SQL_OPTIMIZER_ENV
V$SQL_PLAN
V$SQL_PLAN_STATISTICS
V$SQL_PLAN_STATISTICS_ALL
V$SQL_REDIRECTION
V$SESSION_CURSOR_CACHE
V$SQL_SHARED_CURSOR
V$SQL_SHARED_MEMORY
V$SQL_WORKAREA
V$SQL_WORKAREA_ACTIVE
V$SQL_WORKAREA_HISTOGRAM
V$SYSTEM_CURSOR_CACHE V$MUTEX_SLEEP(10.2)
V$MUTEX_SLEEP_HISTORY(10.2) (for those dubious shared latches)
V$SQL_BIND_CAPTURE(10.1)
V$SQL_JOIN_FILTER(10.2)
V$SQL_AREA_PLAN_HASH(10.2)
V$SQLSTATS(10.2)
V$SYS_OPTIMIZER_ENV(10.1)
V$VPD_POLICY

Database instances Information related to the actual database instance

V$ACTIVE_INSTANCES
V$BGPROCESS
V$DATABASE
V$INSTANCE
V$PROCESS
V$SGA
V$SGASTAT
V$BLOCKING_QUIESCE(10.2)
V$CLIENT_STATS(10.1)
RAC Views: V$BH
V$ACTIVE_INSTANCES

Direct Path Operations Information related to the SQL*Loader direct load option

V$LOADISTAT
V$LOADPSTAT

Distributed/Heterogeneous Services

V$DBLINK
V$GLOBAL_TRANSACTION
V$GLOBAL_BLOCKED_LOCKS
V$HS_AGENT
V$HS_PARAMETER
V$HS_SESSION

Fixed view Information related to the v$ tables themselves

V$FIXED_TABLE
V$FIXED_VIEW_DEFINITION
V$INDEXED_FIXED_COLUMN

General General information related to various system information

V$DBPIPES
V$CONTEXT
V$GLOBALCONTEXT
V$LICENSE
V$OPTION
V$RESERVED_WORDS
V$TIMER
V$TIMEZONE_NAMES
V$TYPE_SIZE
V$_SEQUENCES
V$VERSION
V$DB_TRANSPORTABLE_PLATFORM(10.2)
V$TRANSPORTABLE_PLATFORM(10.1)
V$SCHEDULER_RUNNING_JOBS(10.2)

I/O Information related to I/O, including files and statistics

V$DBFILE
V$FILESTAT
V$WAITSTAT
V$TEMPSTAT
V$FILE_HISTOGRAM(10.1)
V$FILEMETRIC(10.1)
V$FILEMETRIC_HISTORY(10.1)
V$SYSAUX_OCCUPANTS(10.1)
V$TABLESPACE
V$TEMP_HISTOGRAM(10.1)
V$TEMP_SPACE_HEADER
V$TEMPFILE
V$TEMPSEG_USAGE

Latches/locks Information related to latches and locks

V$ENQUEUE_LOCK
V$ENQUEUE_STAT
V$EVENT_NAME
V$FILE_CACHE_TRANSFER
V$GLOBAL_BLOCKED_LOCKS
V$LATCH
V$LATCHHOLDER
V$LATCHNAME
V$LATCH_CHILDREN
V$LATCH_MISSES
V$LATCH_PARENT
V$LOCK
V$LOCKED_OBJECT
V$RESOURCE
V$RESOURCE_LIMIT
V$TRANSACTION_ENQUEUE
V$_LOCK
V$_LOCK1
V$ENQUEUE_STATISTICS

FOLLOWING ARE RAC VIEWS:

V$CR_BLOCK_SERVER
V$GCSHVMASTER_INFO
V$GCSPFMASTER_INFO
V$GC_ELEMENT
V$GES_BLOCKING_ENQUEUE
V$GES_ENQUEUE
V$HVMASTER_INFO
V$GES_LATCH
V$GES_RESOURCES

Log Miner Information related to Log Miner

V$LOGMNR_CALLBACK
V$LOGMNR_CONTENTS
V$LOGMNR_DICTIONARY
V$LOGMNR_LATCH
V$LOGMNR_LOGFILE
V$LOGMNR_LOGS
V$LOGMNR_PARAMETERS
V$LOGMNR_PROCESS
V$LOGMNR_REGION

V$LOGMNR_SESSION
V$LOGMNR_STATS
V$LOGMNR_TRANSACTION
V$LOGMNR_DICTIONARY_LOAD(10.2)

Metrics Information related to Metrics (ALL New in 10g!)

V$METRICNAME
V$SERVICEMETRIC
V$EVENTMETRIC
V$FILEMETRIC
V$FILEMETRIC_HISTORY
V$SERVICEMETRIC_HISTORY
V$SESSMETRIC
V$SYSMETRIC
V$SYSMETRIC_HISTORY
V$SYSMETRIC_SUMMARY
V$THRESHOLD_TYPES
V$WAITCLASSMETRIC
V$WAITCLASSMETRIC_HISTORY

Multithreaded/shared servers Information related to multithreaded and parallel servers, including connections, queues,dispatchers, and shared servers

V$CIRCUIT
V$DISPATCHER
V$DISPATCHER_RATE
V$QUEUE
V$QUEUEING_MTH
V$REQDIST
V$SHARED_SERVER
V$SHARED_SERVER_MONITOR
V$DISPATCHER_CONFIG(10.1)

Object Usage Information related to object use and dependencies

V$OBJECT_DEPENDENCY
V$OBJECT_USAGE

Overall system Information related to the overall system performance

V$GLOBAL_TRANSACTION
V$SHARED_POOL_RESERVED
V$RESUMABLE
V$SORT_SEGMENT
V$TEMPSEG_USAGE
V$STATNAME
V$SYS_OPTIMIZER_ENV
V$SYS_TIME_MODEL
V$SYSSTAT
V$SYSTEM_CURSOR_CACHE
V$SYSTEM_EVENT
V$TEMPFILE
V$TEMPORARY_LOBS
V$TEMP_EXTENT_MAP
V$TEMP_EXTENT_POOL
V$TEMP_SPACE_HEADER
V$TRANSACTION
V$ALERT_TYPES(10.1)
V$EVENT_HISTOGRAM(10.1)
V$OSSTAT(10.1)
V$SYSTEM_WAIT_CLASS(10.1)
V$TEMP_HISTOGRAM(10.1)
V$XML_AUDIT_TRAIL

Parallel Query Information related to the Parallel Query option

V$EXECUTION
V$PARALLEL_DEGREE_LIMIT_MTH
V$PQ_SESSTAT
V$PQ_SLAVE
V$PQ_SYSSTAT
V$PQ_TQSTAT
V$PX_PROCESS
V$PX_PROCESS_SYSSTAT
V$PX_SESSION
V$PX_SESSTAT

Parameters Information related to various Oracle parameters, including initialization and NLS per session

V$NLS_PARAMETERS
V$NLS_VALID_VALUES
V$OBSOLETE_PARAMETER
V$PARAMETER
V$PARAMETER2
V$SPPARAMETER
V$SYSTEM_PARAMETER
V$SYSTEM_PARAMETER2
V$PARAMETER_VALID_VALUES(10.2)

Redo logs Information related to redo logs, including statistics and history

V$LOG
V$LOGFILE
V$LOGHIST
V$LOG_HISTORY
V$THREAD (RAC related)

Replication and materialized views Information related to replication and materialized views

V$MVREFRESH
V$REPLPROP
V$REPLQUEUE

Resource Manager Information related to resource management

V$ACTIVE_SESSION_POOL_MTH
V$ACTIVE_SESSION_POOL_HISTORY
V$RSRC_CONS_GROUP_HISTORY(10.2)
V$RSRC_CONSUMER_GROUP
V$RSRC_CONSUMER_GROUP_CPU_MTH
V$RSRC_PLAN
V$RSRC_PLAN_CPU_MTH
V$RSRC_PLAN_HISTORY(10.2)
V$RSRC_SESSION_INFO(10.2)

Rollback segments And Undo Information on rollback segments, including statistics and transactions

V$ROLLNAME
V$ROLLSTAT
V$TRANSACTION
V$UNDOSTAT

Security/privileges Information related to security

V$ENABLEDPRIVS
V$PWFILE_USERS
V$VPD_POLICY
V$WALLET(10.2)
V$XML_AUDIT_TRAIL(10.2)

Sessions (includes some replication information and heterogeneous services) Information related to a session, including object access, cursors, processes, and statistics

V$ACTIVE_SESSION_HISTORY
V$MYSTAT
V$PROCESS
V$SESS_TIME_MODEL
V$SESSION
V$SESSION_CONNECT_INFO
V$SESSION_CURSOR_CACHE
V$SESSION_EVENT
V$SESSION_LONGOPS
V$SESSION_OBJECT_CACHE
V$SESSION_WAIT
V$SESSION_WAIT_CLASS
V$SESSION_WAIT_HISTORY
V$SESSTAT
V$SESS_IO
V$SES_OPTIMIZER_ENV
V$SESSMETRIC
and V$CLIENT_STATS
and V$TSM_SESSIONS(10.2)

Services (all new for 10.1)

V$ACTIVE_SERVICES
V$SERV_MOD_ACT_STATS
V$SERVICE_EVENT
V$SERVICE_STATS
V$SERVICE_WAIT_CLASS
V$SERVICES

Sorting Information related to sorting

V$SORT_SEGMENT
V$TEMPSEG_USAGE
V$TEMP_EXTENT_MAP
V$TEMP_EXTENT_POOL
V$TEMP_HISTOGRAM(10.1)
V$TEMP_SPACE_HEADER
V$TEMPFILE
V$TEMPSTAT

Standby databases (Data Guard) Information related to standby databases

V$DATAGUARD_STATUS
V$LOGSTDBY
V$LOGSTDBY_STATS
V$MANAGED_STANDBY
V$STANDBY_LOG
V$DATAGUARD_CONFIG(10.1)
V$DATAGUARD_STATS(10.2)
V$LOGSTDBY_PROCESS(10.2)
V$LOGSTDBY_PROGRESS(10.2)
V$LOGSTDBY_STATE(10.2)
V$LOGSTDBY_TRANSACTION(10.2)

File mapping interface Information related to file mapping

V$MAP_COMP_LIST
V$MAP_ELEMENT
V$MAP_EXT_ELEMENT
V$MAP_FILE
V$MAP_FILE_EXTENT
V$MAP_FILE_IO_STACK
V$MAP_LIBRARY
V$MAP_SUBELEMENT

Streams Information related to streams

V$AQ
V$STREAMS_APPLY_COORDINATOR
V$STREAMS_APPLY_READER
V$STREAMS_APPLY_SERVER
V$STREAMS_CAPTURE
V$BUFFERED_PUBLISHERS(10.1)
V$BUFFERED_QUEUES(10.1)
V$BUFFERED_SUBSCRIBERS(10.1)
V$PROPAGATION_RECEIVER(10.1)
V$PROPAGATION_SENDER(10.1)
V$RULE(10.1)
V$RULE_SET(10.1)
V$RULE_SET_AGGREGATE_STATS(10.1)
V$STREAMS_TRANSACTION(10.2)

Statistics Information related to statistics in general

V$SEGMENT_STATISTICS
V$SEGSTAT
V$SEGSTAT_NAME
V$STATISTICS_LEVEL
V$STATNAME
V$WAITSTAT

Transactions Information related to transactions in general

V$GLOBAL_TRANSACTION
V$LOGSTDBY_TRANSACTION
V$RESUMABLE
V$STREAMS_TRANSACTION
V$TRANSACTION
V$TRANSACTION_ENQUEUE





Note: In Oracle 10.1 and above, the V$LOCK_TYPE dynamic performance view summarizes all implemented lock types

MTV ENJOY....

1 comment:

Blogger said...

DreamHost is definitely the best hosting provider for any hosting plans you might require.