The following V$ Views are part of the data dictionary which are commonly used by DBA’s
One can view the columns of these views by
SQL>desc v$
1. V$archive_dest: Shows all archive redo log destinations. Use this view to find out which place archived redo logs are copied: select dest_id, destination from v$archive_dest.
2. V$archive_dest_status: this view allows to find status and error 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 logs that are known to be missing for each thread on a physical standby database (highest gap pnly)
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$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
8. V$buffer_pool
9. V$buffer_pool_statistics
10. V$database: This views allows you to access database information. For example you can check whether the database is in archivelog mode or not.
SQL> select log_mode from v$database;
Log mode
Archivelog
11. V$datafile: This view contains an entry for each datafile of the database.
SQL> select name from v$datafile;
12. V$datafile_header:
13. V$dataguard_status: shows error messages in the dataguard environment.
14. V$db_object_cache:this view displays objects that are cached(pinned) in the library cache.
15. V$enqueue_stat: If there are a lot of enqueue waits in v$session_event or v$system_event, v$enqueue_stst 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.
16. V$eventmetric: This view is new in Oracle 10g and allows improved timings and statistics.
17. V$event_name: Contains a record for each wait event
18. V$filemetric: This view is new in Oracle 10g and allows improved timings and statistics.
19. V$filestat
20. V$fixed_table: This view contains the name of all v$, GV$, X$ tables.
21. V$flash_recovery_area_usage: It contains following columns
File_type, percent_space_used, percent_space_reclaimabl,number_of_files.
22. V$instance: it contains information about instance. These are the columns present in v$instance—instance_number, instance_name,host_name,version,startup_time,status,paralled,thread#,archiver,log_switch_wait,logins,shutdown_pending, database_status,instance_status,instance_role, active_state,blocked.
23. V$instance_recovery: It can be used to determine the optimal size of the redo logs.
24. 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'
25. V$librarycache
26. 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. The possible values for lmode and request are: null, row share(SS), Row Exclusive (SX), share(S), Sahre Row Exclusive (SSX) and Exclusive (X). If the lock type is TM, the column id1 is the object's id and the name of the object can then be queried like so: select name from sys.obj$ where obj# = id1
27. V$locked_object: Who is locking and what?
Use this query to find out who is locking and wht
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.
28. V$log : contains information on each log group. Common values for status column are unused, current, active, inactive.
29. V$logfile: this view can be queried to find the filenames, group numbers, and states of redo logfiles. For example to find all files of group 2 use
Select member from v$logfile wher group# = 2
30. V$log_history: This view contains an entry for each log switch that occurred. The column first_time indicates the time of the first entry
On physical standby database , this view shows applied logs.
31. V$logstdby: Can be used to verify that archived redo logs are being applied to standby databases.
32. V$managed_standby: monitors the progress of a standby database in managed recovery mode, it displays information about the activities of log transport service and log apply service.
33. V$nls_parameters: The NLS parameters that are in effect for the session quering this view. This view NLS_SESSION_PARAMETERS is based on v$nls_parameters.
34. V$object_usage: This gathers information about used indexes when an index is monitored using alter index monitoring usage.
35. V$open_cursor
36. V$option: This view lets you see which option are installed in the server.
37. V$Parameter: lists the name-value pairs of the init.ora file. For example if you want to find the db block size use the following query
Select value from v$parameter where name =’db_blokc_size.
38.
39. V$pgastat
40. V$Process: join v$process’s addr with v$session paddr
The column traceif is equal to the value used in alter session set.
41. V$pwfile_users: Lists all users who have been granted sysdba or sysoper privileges.
42. V$recover_file: useful to find out which datafiles need recovery.
43. V$recovery_file_dest:
44. V$reserved_words
45. V$resource_limit: these are the following columns resource_name, current_utilization, max_utilization, initial_allocation, limit_value.
46. V$rollname: the name of the online rollback segments. .This view’s usn filed can be joined with v$rollstats’s usn field and with v$transaction’s xidusn field
47. V$transaction can be used to tract undo by session.
48. V$rollstat: statistics for rollback segments
49. V$session: It contains all information about present session. A record in v$session contains sid and serial#. These numbers can be used kill a session (alter system kill session). Query v$session to find the necessary columns.
Some of the important columns are : SID, Serial#, user, username, lockwait,server,status,osuser,process, blocking_session, blocking_instance, event, sql_trace.
Join sid with v$sesstat if you want to get some statistical information for a particular sesssion.
50. V$sessmetric: This view is new in Oracle 10g and allows improved timing and statistics.
51. V$session_event: this view is similar to v$system_event. However, it breaks it down to currently connected sessions.
V$session_event has also the columns max_wait that shows the maximum time waited for a wait event.
52. 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. If there is any procedure which is running it will report its progress in v$session_longops. The procedure will also set the module attribute in v$session which makes it possible to find the sid, serial # of the session.
For example : If the procedure long_proc is run, you can issue the following query to get feedback on its progress:
Use the following sql statement:
Select time_remaining, sofar,elapsed_seconds from v$session_lonops l, v$session s where l.sid =s.sid and l.serial# =s.serial# and s.module =’long_proc’
53. 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. The columns P1, P2 and P3 are parameters that are dependant on the event. With Oracle 10g, v$session_wait's information will be exposed within v$session as well. Since 10g, Oracle displays the v$session_wait information also in the v$session view.
54. V$session_wait_history: This view is new in oracle 10g and allows improved timing and statistics.
55. V$sesstat: This view is similar to v$mystat except that it shows cumulated statistics for all sessions.
Join sid with v$session and join statistic# with v$statname.
V$sesstat is also similar to v$systat except that v$sysstat accumulated the statistics as soon as a session terminated.
56. V$sga: shows how much memory the shared global area uses.
Selecting * from v$sga is roughly the same as typing show sga
57. V$sgastat: shows free space in sga
58. V$sga_dynamic_components: it contains information about sga resize operation since startup. This is also used to find the granule size of SGA.
59. V$sga_resize_ops
60. V$sort_usage:
61. V$sort_segments
62. V$spparameter: Returns the values for spfile.
63. V$SQL: v$sql is same as 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.
64. V$sqlarea: It tells you the sql text of currently running sql statements.
The field version_count indicated how many versions an sql statement has
Select sql_text from v$sqlarea where users_executing > 0;
65. V$sqL text,
66. V$sql_plan
67. V$sql_text_with_newlines
68. V$sql_bind_data:
69. V$sql_bind_capture: This view captures bind variables for all sessions.
70. V$sql_cursor
71. V$sqL_workarea
72. V$standby_log
73. V$statname: use this view to get the decoded names for the statistic# filed of v$sysstat , v$mystat, and sessstat
74. V$sysmetric: This view is new in oracle 10g and allows improved timing and statistics.
75. V$sysmetric_history: New in oracle 10g and allows improved timings and statistcs.
76. $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.
77. 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.
78. V$undostat
79. V$tempfile
80. V$tempstat
81. V$thread
82. V$timer
83. V$transaction
84. V$trasnportable_platform
85. V$version: use this view to find out what version you actually work on
select * from v$version
86. V$waitstat
Regards,
Sowmya (OSR)
http://usdba.blogspot.com/2010/03/oracle-v-views.html
