Dynamic Views

The Oracle server provides a number of standard views to obtain information on the database and instance. These views include:

 

•V$SGA: Queries the size of the instance for the shared pool, log buffer, data buffer cache, and fixed memory sizes (operating system-dependent).

•V$INSTANCE: Queries the status of the instance, such as the instance mode, instance name, startup time, and host name.

•V$PROCESS: Queries the background and server processes created for the instanc.

•V$BGPROCESS: Queries the background processes created for the instance.

•V$DATABASE: Lists status and recovery information about the database. It includes information on the database name, the unique database identifier, the creation date, the control file creation date and time, the last database checkpoint, and other information. 

•V$DATAFILE: Lists the location and names of the datafiles that are contained in the database. It includes information relating to the file number and name, creation date, status (online or offline), enabled (read-only, read-write), last datafile checkpoint, size, and other information.

  

       •V$LOG: Lists the number of members in each group. It contains:

-The group number

-The current log sequence number

-The size of the log

-The number of members

-Status (ACTIVE, CLEARING, CLEARING_CURRENT,
     CURRENT, INACTIVE or UNUSED)

         -Lowest SCN in the log

•V$LOGFILE: It contains:

-Group number for each member

-Status (STALE, INVALID,DELETED, or blank)

-Redo log member names

•V$LOG_HISTORY: Contains information on log history from the control file.

  •V$FAST_START_SERVERS: Provides information about all the recovery slaves performing parallel transaction recovery.

   •V$FAST_START_TRANSACTIONS: Contains information about the progress of the transactions that Oracle is recovering.

 

ARCHIVED LOG FILES: - 

To view information about the archived log files use following views: 

  

•V$ARCHIVED_LOG: Displays archived log information from the control file. 

•V$ARCHIVE_DEST: For the current instance, describes all archive log destinations, the current value, mode, and status. 

•V$ARCHIVE_PROCESSES: Provides information about the state of the various ARCH processes for the instance.

•V$DATABASE: Current state of archiving.
 

•V$LOG_HISTORY: Contains log file information from the control file.

•V$BACKUP_CORRUPTION  

•V$COPY_CORRUPTION

•V$BACKUP_DEVICE displays information about supported backup devices. If a device type does not support named devices, then one row with the device type and a null device name is returned for that device type. If a device type supports named devices then one row is returned for each available device of that type. The special device type DISK is not returned by this view because it is always available.

•V$CONTROLFILE view to display the names of all control files.

•V$LOGFILE view to display the names of all redo log files.

•V$TABLESPACE and •V$DATAFILE data dictionary views to obtain a list of all datafiles and their respective tablespaces

•V$BACKUP view to determine which files are in backup mode
    

The following views to obtain RMAN information stored in the control file:

•V$ARCHIVED_LOG shows which archives have been created, backed up, and cleared in the database.

•V$BACKUP_CORRUPTION shows which blocks have been found corrupt during a backup of a backup set.

•V$COPY_CORRUPTION shows which blocks have been found corrupt during an image copy.

•V$DATABASE_BLOCK_CORRUPTION displays information about database blocks that were corrupted after the last backup.

•V$BACKUP_DATAFILE is useful for creating equal-sized backup sets by determining the number of blocks in each datafile. It can also help you find the number of corrupt blocks for the datafile.
•V$BACKUP_REDOLOG shows archived logs stored in backup sets. 

•V$BACKUP_SET shows backup sets that have been created.

•V$BACKUP_PIECE shows backup pieces created for backup sets. displays information about backup pieces from the control file. Each backup set consists of one or more backup pieces.


•V$PROCESS and V$SESSION to determine which sessions correspond to which RMAN channels. 

•V$SESSION_LONGOPS to monitor the progress of backups and copies. 

•V$BACKUP_CORRUPTION for backup sets. 

•V$COPY_CORRUPTION for image copies.

•V$EVENT_NAME for complete listing of wait events

•V$ACTIVE_INSTANCES displays the mapping between instance names and instance
numbers for all instances that have the database currently mounted.

•V$ACCESS displays information about locks that are currently imposed on library
cache objects. The locks are imposed to ensure that they are not aged out of the library
cache while they are required for SQL execution.
 •V$ACTIVE_SERVICES displays the active services in the database.

•V$ACTIVE_SESS_POOL_MTH displays available active session pool resource allocation methods.

•V$ACTIVE_SESSION_HISTORY displays sampled session activity in the database. It
contains snapshots of active database sessions taken once a second.

•V$ADVISOR_PROGRESS displays information about the progress of advisor execution.
•V$ALERT_TYPES displays information about server alert types.

•V$AQ displays statistics for the queues in the database.

•V$ARCHIVE displays information about redo log files in need of archiving. Each row displays information for one thread. This information is also available in V$LOG.

•V$SESSION_WAIT displays the events for which sessions have just completed
waiting or are currently waiting.

•V$SYSTEM_EVENT displays the total number of times all the sessions have waited
for the events in that view.

•V$SESSION_EVENT is similar to •V$SYSTEM_EVENT, but displays all waits for
each session.
 •V$STATNAME view contains all of the statistics for an Oracle release.

•V$SESSTAT view displays statistics on a per-session basis and is valid only for the
session currently connected. When a session disconnects, all statistics for the session
are updated in V$SYSSTAT. The values for the statistics are cleared until the next
session uses them. 



•V$SYSSTAT: Total number of cursors opened since the instance started

•V$BACKUP displays the backup status of all online datafiles.

•V$BACKUP_ARCHIVELOG_DETAILS contains information about all restorable archive logs. It will include all archived logs backed up in a backup set or proxy copies.

•V$BACKUP_ARCHIVELOG_SUMMARY provides archive log summary information based on archive logs in the backup set or on proxy copies.

•V$BACKUP_ASYNC_IO displays performance information about ongoing and recently completed RMAN backups and restores.

•V$BACKUP_CONTROLFILE_DETAILS contains information about restorable control files. It will include all the control files backed up in the backup set, image copies, and
proxy copies.

•V$BACKUP_CONTROLFILE_SUMMARY provides control file summary information, based on either a backup set of files, image copies, or proxy copies.

•V$BACKUP_COPY_DETAILS contains information about all available control file and datafile copies.

•V$BACKUP_COPY_SUMMARY provides summary information for the output datafile and control file copy.

•V$BACKUP_CORRUPTION displays information about corrupt block ranges in datafile backups from the control file.

•V$BACKUP_DATAFILE displays information about control files and datafiles in

backup sets from the control file.

•V$BACKUP_DATAFILE_DETAILS contains information about restorable datafiles. It
will include all datafiles backed in the backup set, image copies, and proxy copies

•V$BACKUP_DATAFILE_SUMMARY provides summary information for a specific criteria
set, based on a backup job, a time range applicable to jobs, or a specific datafile).

•V$BACKUP_DEVICE displays information about supported backup devices. If a device
type does not support named devices, then one row with the device type and a null
device name is returned for that device type. 

•V$BACKUP_FILES displays information about all RMAN backups (both image copies
and backup sets) and archived logs.

•V$BACKUP_PIECE displays information about backup pieces from the control file.
Each backup set consists of one or more backup pieces.

•V$BACKUP_PIECE_DETAILS displays information about all available backup pieces.

•V$BACKUP_REDOLOG displays information about archived logs in backup sets from
the control file.

•V$BACKUP_SET displays information about backup sets from the control file.

•V$BACKUP_SET_DETAILS provides detailed information about the backup set. This
view will contain an extra row for each backup session that invokes BACKUP
BACKUPSET

•V$BACKUP_SET_SUMMARY provides summary information for a backup set.

•V$BACKUP_SPFILE displays information about server parameter files in backup sets
from the control file.

•V$BACKUP_SPFILE_DETAILS displays information about all restorable SP files
backed up in the backup set.

•V$BACKUP_SPFILE_SUMMARY provides summary information for input SP file, based
on either a backup job or time range applicable to jobs.

•V$BACKUP_SYNC_IO displays performance information about ongoing and recently
completed RMAN backups and restores.

•V$BGPROCESS displays information about the background processes.

•V$BH   This is a Real Application Clusters view. This view gives the status and number of
pings for every buffer in the SGA.

•V$BLOCK_CHANGE_TRACKING displays the status of block change tracking for the

database.

•V$BLOCKING_QUIESCE indicates if a session is blocking, or would block, a quiesce
operation.

•V$BUFFER_POOL displays information about all buffer pools available for the
instance.

•V$BUFFER_POOL_STATISTICS displays statistics about all buffer pools available for
the instance.

•V$BUFFERED_PUBLISHERS displays information about all buffered publishers in the
instance. There is one row per queue per sender.

•V$BUFFERED_QUEUES displays information about all buffered queues in the instance.
There is one row per queue.

•V$BUFFERED_SUBSCRIBERS displays information about the subscribers for all
buffered queues in the instance. There is one row per subscriber per queue.

•V$CACHE This is a Real Application Clusters view. This view contains information from the block header of each block in the SGA of the current instance as related to particular
database objects.

•V$CACHE_LOCK view is deprecated.

•V$CACHE_TRANSFER view is identical to the V$CACHE view but only displays blocks that have been pinged at least once.

•V$CIRCUIT contains information about virtual circuits, which are user connections to
the database through dispatchers and servers.

•V$CLASS_CACHE_TRANSFER is deprecated.

•V$CLIENT_STATS displays measures for all sessions that are active for the client
identifier per instance. The statistics available in this view are a subset of those
available in V$SESSTAT and V$SESS_TIME_MODEL.

•V$CLUSTER_INTERCONNECTS displays one or more interconnects that are being used for cluster communication.

•V$CONFIGURED_INTERCONNECTS displays all the interconnects that Oracle is aware of. This view attempts to answer the question of where Oracle found the information
about a specific interconnect.

•V$CONTEXT This view lists set attributes in the current session.

•V$CONTROLFILE This view lists the names of the control files.

•V$CONTROLFILE_RECORD_SECTION displays information about the control file
record sections.

•V$COPY_CORRUPTION This view displays information about datafile copy corruptions from the control file.

•V$CR_BLOCK_SERVER This view displays statistics on the Global Cache Service processes (LMS) used in cache fusion.

•V$CURRENT_BLOCK_SERVER displays statistics on the Global Cache Service processes (lMS) used in cache fusion.

•V$DATABASE displays information about the database from the control file.

•V$DATABASE_BLOCK_CORRUPTION displays information about database blocks that were corrupted after the last backup.

•V$DATABASE_INCARNATION displays information about all database incarnations.
Oracle creates a new incarnation whenever a database is opened with the RESETLOGS
option.

•V$DATAFILE  This view contains datafile information from the control file.

•V$DATAFILE_COPY This view displays datafile copy information from the control file.

•V$DATAFILE_HEADER This view displays datafile information from the datafile headers.

•V$DATAGUARD_CONFIG displays the unique database names defined with the DB_
UNIQUE_NAME and LOG_ARCHIVE_CONFIG initialization parameters, providing a
view of the Data Guard environment from any database in the configuration.

•V$DATAGUARD_STATS displays how much redo data generated by the primary
database is not yet available on the standby database, showing how much redo data
could be lost if the primary database were to crash at the time you queried this view.

•V$DATAGUARD_STATUS displays and logs events that would typically be triggered by any message to the alert log or server process trace files.

•V$DB_CACHE_ADVICE contains rows that predict the number of physical reads for the cache size corresponding to each row.

•V$DB_OBJECT_CACHE  This view displays database objects that are cached in the library cache. Objects include tables, indexes, clusters, synonym definitions, PL/SQL procedures and packages, and triggers.

•V$DB_PIPES This view displays the pipes that are currently represented in the shared pool for this instance.

•V$DB_TRANSPORTABLE_PLATFORM displays all platforms to which the database can be transported using the RMAN CONVERT DATABASE command. The transportable database feature only supports transports of the same Indian platform. Therefore, V$DB_TRANSPORTABLE_PLATFORM displays fewer rows than V$TRANSPORTABLE_PLATFORM.

•V$DBFILE
This view lists all datafiles making up the database. This view is retained for historical
compatibility. Use of V$DATAFILE is recommended instead.

•V$DBLINK This view describes all database links (links with IN_TRANSACTION = YES) opened by the session issuing the query on V$DBLINK. These database links must be
committed or rolled back before being closed.

•V$DELETED_OBJECT This view displays information about deleted archived logs, datafile copies and backup pieces from the control file.

•V$DISPATCHER displays information about the dispatcher processes.

•V$DISPATCHER_CONFIG displays information about the dispatcher configurations
and their attributes.

•V$DISPATCHER_RATE displays rate statistics for a number of activities performed by
the dispatcher processes.

Regards,

Sukhwinder Singh

.