Oracle Views Chart

Oracle Views and their Description

Sno#	View Name	Description
1	v$archive_dest	Shows all archived redo log destinations. Use this view to find out to which place archived redo logs are copied: select dest_id,destination from v$archive_dest. These values correspond to the init parameter log_archive_dest_n.
2	v$archive_dest_status	This view allows to find status and errors for each of the defined
3	v$archived_log	Displays successfully archived redo logs.shows received logs on a primary standby database.
4	v$archive_gap	Lists sequence numbers of the archived los that are known to be missing for each thread on a (physical?) standby database (highest gap only).
5	v$archive_processes	This view provides information on the archive processes. It can be used to find out if an ARCH process is active or not.
6	v$controlfile	Displays the location and status of each controlfile in the database.
7	v$controlfile_record_section	The minimum number of days that a reusable record is kept in the controlfile is controlled by the control_file_record_keep_time parameter.These sections consist of records. The size, total number and number of used record are exposed through v$controlfile_record_section.
8	v$bh	This dynamic view has an entry for each block in the database buffer cache.
		The column status can be: free - This block is not in use, xcur - Block held exclusively by this instance, scur - Block held in cache, shared with other instance, cr - Block for consistent read, read- Block being read from disk, mrec - Block in media recovery mode, irec - Block in instance (crash) recovery mode.
9	v$buffer_pool	See buffer pools.This view's column id can be joined with x$kcbwds.indx, See also x$kcbwbpd
10	v$buffer_pool_statistics
11	v$database	This view lets you access database information. For example, you can check (using log_mode) whether or not the database is in archivelog mode:
		checkpoint_change# records the SCN of the last checkpoint.
		switchover_status: can be used to determine if it is possible to perform a switchover operation Only available for physical standby databases.
		See protection modes in data guard for the columns protection_mode and protection_level.
12	v$datafile	This view contains an entry for each datafile of the database.This view can be used to find out which datafiles must be backed up in a cold backup: select name from v$datafile
13	v$datafile_header	Various information about datafile headers. For example, if you're interested in when the a file's last checkpoint
14	v$dataguard_status	Shows error messages in a data guard environment.
15	v$db_object_cache	This view displays objects that are cached (pinned) in the library cache. See also dbms_shared_pool.
16	v$enqueue_stat	If there are a lot of enqueue waits "in" v$session_event or v$system_event, v$enqueue_stat allows to break down those enqueues in enqueue classes. For each such class, the gets, waits, failures and the cumulative sum of waited time can be found. For a list of enqueue types, refer to enqueue types in x$ksqst. The column cum_wait_time stems from x$ksqst.ksqstwtim.
17	v$eventmetric	This view is new in Oracle 10g and allows improved timing and statistics.
18	v$event_name	Contains a record for each wait event.
19	v$filemetric	This view is new in Oracle 10g and allows improved timing and statistics.
20	v$filestat
21	v$fixed_table	This view contains the name of all V$, X$ and GV$ tables. In oracle 8.1.7, there are 187 different v$ tables
22	v$fixed_view_definition	Contains the defintion in its attribute view_definition for the views of v$fixed_table.
23	v$flash_recovery_area_usage	See also v$recovery_file_dest
24	v$instance	instance_role can be used to determine if an instance is an active instance (=primary instance) or a secondary instance (in a standby environment. dbms_utility.db_version can be used to retrieve the same version as the field version in v$instance.
25	v$instance_recovery	Can, for example, be used to determine the optimal size of redo logs.
26	v$latch	Oracle collects statistics for the activity of all latches and stores these in this view. Gets is the number of successful willing to wait requests for a latch. Similarly, misses is how many times a process didn't successfully request a latch. Spin_gets: number of times a latch is obtained after spinning at least once. Sleeps indicates how many times a willing to wait process slept. Waiters_woken tells how often a sleeping process was 'disturbed'.
27	v$librarycache
28	v$lock	This view stores all information relating to locks in the database. The interesting columns in this view are sid (identifying the session holding or aquiring the lock), type, and the lmode/request pair.
		Important possible values of type are TM (DML or Table Lock), TX (Transaction), MR (Media Recovery), ST (Disk Space Transaction).
		Exactly one of the lmode, request pair is either 0 or 1 while the other indicates the lock mode. If lmode is not 0 or 1, then the session has aquired the lock, while it waits to aquire the lock if request is other than 0 or 1
29	v$locked_object	select   oracle_username, os_user_name, locked_mode,  object_name, object_type from   v$locked_object a,dba_objects b where a.object_id=b.object_id
30	v$log	Contains information on each log group. See also online redo log.
31	v$logfile	his view can be queried to find the filenames, group numbers and states of redo log files. For example, to find all files of group 2, use select member from v$logfile where group# = 2
32	v$logmnr_contents	See dbms_logmnr.
33	v$log_history	This view contains an entry for each Log Switch that occured. The column first_time indicates the time of the first entry??? On physical standby databases, this view shows applied logs.
34	v$logstdby	Can be used to verify that archived redo logs are being applied to standby databases.
35	v$managed_standby	Monitors the progress of a standby database in managed recovery mode, more exactly, it displays information about the activities of log transport service and log apply service.
36	v$mystat	This view records statistical data about the session that accesses it. Join statistic# with v$statname. v$sesstat is also similar to v$sysstat, except that v$sysstat accumulates the statistics as soon as a session terminates.
37	v$nls_parameters	The NLS parameters that are in effect for the session quering this view. The view NLS_SESSION_PARAMETERS is based on v$nls_parameters. See also v$nls_valid_values.
38	v$nls_valid_values	This view can be used to obtain valid values for NLS parameters such as Supported Character Sets, Languages, Territories, Sorting Orders
39	v$object_usage	v$object_usage gathers information about used (accessed) indexes when an index is monitored using alter index ... monitoring usage.
40	v$open_cursor
41	v$option	This view lets you see which options are installed in the server.
42	v$parameter	Lists the name-value pairs of the init.ora file (or their default, if not in the init.ora)
43	v$pgastat	See also pga.
44	v$process	Join v$process's addr with v$session paddr.The column traceid is equal to the value used in alter session set .
45	v$pwfile_users	Lists all users who have been granted sysdba or sysoper privileges. See adding user to a password file.
46	v$recover_file	Useful to find out which datafiles need recovery.Join with v$datafile to see filenames instead of numbers....
47	v$recovery_file_dest	See also v$flash_recovery_area_usage
48	v$reserved_words	This view can be consulted if one is in doubt wheter a particular word is a reserved word (for example when writing PL/SQL Code or assigning a password to a user).
49	v$resource_limit
50	v$rollname	he names of online rollback segments. This view's usn field can be joined with v$rollstat's usn field and with v$transaction's xidusn field. v$transaction can be used to track undo by session.
51	v$rollstat	Statistics for rollback segements
52	v$session	Join sid with v$sesstat if you want to get some statistical information for a particular sesssion. A record in v$session contains sid and serial#. These numbers can be used kill a session (alter system kill session).
53	v$sessmetric	This view is new in Oracle 10g and allows improved timing and statistics.
54	v$session_event	This views is similar to v$system_event. However, it breaks it down to currently connected sessions.
55	v$session_longops	Use v$session_longops if you have a long running pl/sql procedure and want to give feedback on how far the procedure proceeded
56	v$session_wait	This views shows what wait event each session is waiting for, or what the last event was that it waited for In contrast, v$session_event lists the cumulative history of events waited for in a session.
57	v$session_wait_history	This view is new in Oracle 10g and allows improved timing and statistics.
58	v$sesstat	Join sid with v$session and join statistic# with v$statname. v$sesstat is also similar to v$sysstat, except that v$sysstat accumulates the statistics as soon as a session terminates.
59	v$sga	Shows how much memory the shared global area uses. Selecting * from v$sga is roughly the same as typing show sga in sql plus with the exeption that the latter also show the total.
60	v$sgastat	Showing free space in the sga: select * from v$sgastat where name = 'free memory'
61	v$sga_dynamic_components	Information about SGA resize operations since startup. This view can also be used to find out the granule size of SGA components.
62	v$sga_resize_ops
63	v$sort_usage	See temporary tablespaces
64	v$sort_segment	See Temporary Tablespaces
65	v$spparameter	Returns the values for the spfile.
66	v$sql	v$sql is similar to v$sqlarea, the main difference being that v$sql drills down to select * from x$kglob whereas v$sqlarea drills down to select sum from x$kglob. See also here
67	v$sqlarea	Join v$sqlarea's address with v$session's sql_address.
68	v$sqltext
69	v$sql_plan	select sql_text,address,hash_value,child_number from v$sql where users_executing > 0;
70	v$sqltext_with_newlines	This view can be used to construct the entire text for each session's actual SQL statement. Use the following statement to to that:
71	v$sql_bind_data	Join cursor_num with cno of v$sql_cursor.
72	v$sql_bind_capture	This view captures bind variables for all sessions and is faster than setting 10046 on level 4
73	v$sql_cursor	Join parent_handle with address of v$sql or v$sqlarea
74	v$sql_workarea	v$sql_workarea can be joined with v$sqlarea on address and hash_value, and it can be joined with v$sql on address, hash_value and child_number
75	v$standby_log
76	v$statname	Use this view to get decoded names for the statistic# field of v$mystat, v$sysstat and v$sesstat.
77	v$sysaux_occupants	v$sysaux_occupants doesn't exist in Oracle versions prior to Oracle 10g.
78	v$sysmetric	This view is new in Oracle 10g and allows improved timing and statistics
79	v$sysmetric_history	This view is new in Oracle 10g and allows improved timing and statistics.
80	v$sysstat	v$sysstat is similar to v$sesstat. While v$sesstat displays statitics for the current session, v$sysstat displays the cumulated statitics since startup of the database. For example, it is possible to find out the CPU time (name = 'CPU used by this session') This view is (among others) used to calculate the Hit Ratio.
81	v$system_event	This view displays the count (total_waits) of all wait events since startup of the instance. If timed_statistics is set to true, the sum of the wait times for all events are also displayed in the column time_waited. The unit of time_waited is one hundreth of a second. Since 10g, an additional column (time_waited_micro) measures wait times in millionth of a second. total_waits where event='buffer busy waits' is equal the sum of count in v$waitstat. v$enqueue_stat can be used to break down waits on the enqueue wait event. While this view totals all events in an instance, v$session_event breaks it down to all currently connected sessions.
82	v$undostat	undo tablespaces
83	v$tempfile
84	v$tempseg_usage	v$tempseg_usage is a public synonym for v$sort_usage.
85	v$tempstat
86	v$thread	The Oracle SID can be retrieved through select instance from v$thread
87	v$timer	This view has only one column (hsecs) which counts hundreths of seconds. Whenever it overflows four bytes, it starts again with 0
88	v$transaction	Important fields of v$transaction are used_ublk and used_urec. They tell of how many blocks and records the undo for a transaction consists. In order to find out the name of the corresponding rollback segemnt, join the xidusn field with the usn field of v$rollname. This is demonstrated in
89	v$timezone_names	See also timezones for some values of tzabbrev.
90	v$transportable_platform	Transportable tablespaces can quickly be moved across different platforms (with Oracle 10g, that is) (using convert tablespace). This allows databases to be migrated at almost the speed of a file transfer. For example, data providers can deploy their data to customers that run Oracle on different platforms more easily. Also, usually, data marts are on different platforms as the data warehouse. With transportable tablespaces, the data can be 'copied' from the data warehouse to the data mart more efficiently. Transportable tablespaces require the export transportable tablespaces option.
91	v$version	Use this view to find out what version you actually work on: select * from v$version;
92	v$waitstat	total_waits where event='buffer busy waits' is equal the sum of count in v$system_event.
	dba_all_tables
	dba_indexes
	dba_ind_partitions
	dba_ind_subpartitions
	dba_object_tables
	dba_part_col_statistics
	dba_subpart_col_statistics
	dba_tables
	dba_tab_cols
	dba_tab_columns
	dba_tab_col_statistics
	dba_tab_partitions
	dba_tab_subpartitions
	dba_tables