To be finished...
The following views are part of the data
dictionary.
See also Oracle's x$ tables
v$archive_destShows 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.
v$archive_dest_statusThis view allows to find status and errors for each of the
defined
v$archived_logDisplays successfully archived
redo logs.
v$archive_gapLists sequence numbers of the archived los that are known
to be missing for each thread on a (physical?) standby database (highest
gap only).
v$archive_processesThis view provides information on the archive processes. It
can be used to find out if an ARCH
process is active or not.
v$controlfileDisplays the location and status of each controlfile in the
database.
v$buffer_poolSee also x$kcbwbpd
v$buffer_pool_statisticsv$databaseThis view lets you access database information. For
example, you can check (using log_mode) whether or not the database is in
archivelog
mode:
ADPDB>select log_mode from v$database; LOG_MODE ------------ ARCHIVELOG 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. Can be:
See protection
modes in data guard for the columns protection_mode and
protection_level.
database_role determines if a database is a primary
or a logical
standby database or a physical
standby database.
force_logging tells if a database is in force logging
mode or not.
v$datafileThis 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 v$datafile_headerVarious information about datafile
headers. For example, if you're interested in when the a file's last
checkpoint
was:
select name, checkpoint_change#, to_char(checkpoint_time, 'DD.MM.YYYY HH24:MI:SS') from v$datafile_header v$dataguard_statusShows error messages in a data guard
environment.
v$db_object_cacheThis view displays objects that are cached (pinned) in the
library
cache. See also dbms_shared_pool.
v$enqueue_statIf 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.
v$eventmetricThis view is new in Oracle 10g and allows
improved
timing and statistics.
v$event_nameContains a record for each event.
v$filemetricThis view is new in Oracle 10g and allows
improved
timing and statistics.
v$filestatv$fixed_tableThis view contains the name of all V$, X$ and
GV$ tables. In oracle 8.1.7, there are 187 different v$ tables:
ORA81> select count(*) from v where name like 'V$%'; COUNT(*) ---------- 185 If you want to know, which x$ tables there are, do a
select name from v$fixed_table where name like 'x$%'; v$fixed_view_definitionContains the defintion in its attribute
view_definition for the views of v$fixed_table.
v$instanceinstance_role can be used to determine if an
instance is an active instance (=primary instance) or a secondary instance
(in a standby
environment.
v$latchOracle 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'.
v$librarycachev$lockThis 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 of the 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
values are: 2: Row Share (RS), 3: Row Exclusive (RX), 4:
Share (S), 5: Share Row Exclusive (SRX), 6:
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 A lock type of JI indicates that a materialized
view is being refreshed.
A more detailed example can be found here
v$locked_objectWho is locking what:
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 v$logContains information on each log group. See also redo log.
Comman values for the status column are:
v$logfileThis 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 v$logmnr_contentsSee dbms_logmnr.
v$log_historyThis 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.
v$logstdbyCan be used to verify that archived redo logs are being
applied to standby databases.
v$managed_standbyMonitors 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.
select process, pid, status, client_process, group# "Stdby Redo Log Gr", block# from v$managed_standby; client_process: the corresponding primary
database process. If lgwr
log transmission is chosen, one row should have client_process=LGWR.
If ARCH transmission is chosen, one row should have ARCH.
v$mystatThis 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. See also recording
statistics with oracle.
v$nls_parametersThe 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.
v$nls_valid_valuesThis view can be used to obtain valid values for NLS
parameters such as
v$open_cursorv$optionThis view lets you see which options are
installed in the server.
v$parameterLists the name-value pairs of the init.ora file
(or their default, if not in the init.ora). For example, if you need to
know what your block size is:
select value from v$parameter where name = 'db_block_size' The columns isses_modifiable and
issys_modifiable can be used to determine if a parameter can be
changed at session level using alter session
or at system level using alter system.
A parameter is modifiable at session level if isses_modifiable = 'TRUE'. A
parameter is modifiable at system level if issys_modifiable = 'DEFERRED'
or issys_modifiable = 'IMMEDIATE'. However, if a parameter is changed at
system level if issys_modifiable = 'DEFERRED' it only affects sessions
that are started after chaning the parameter. Additionally, the alter
system set ... deferred option must be used.
There are also some undocumented
(or hidden?) parameters.
v$pga_statSee also pga.
v$processJoin v$process's addr with v$session's paddr.
v$pwfile_usersLists all users who have been granted sysdba or sysoper
privileges. See adding
user to a password file.
v$recover_fileUseful to find out which datafiles
need recovery.
Join with v$datafile
to see filenames instead of numbers....
v$reserved_wordsThis view can be consulted if one is in doubt wheter a
particular word is a reserved word (for example when writing PL/SQL Code.
The view only consist of two columns: keyword and length.
v$resource_limitv$rollnameThe 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.
The following statement shows the rollback segment for all
sessions that use undo information.
select s.sid, r.name from v$session, v$transaction, v$rollname where t.addr = s.taddr and r.usn = t.xidusn; v$rollstatStatistics for rollback segements
v$sessionThe column audsid can be joined with sys_context('userenv','SESSIONID')
to find out which session is the "own one". Alternatively, dbms_support.mysid
can be used.
The fields module and action of v$session can
be set with dbms_application_info.set_module.
(See v$session_longops
for an example.
The field client_info can be set with dbms_application_info.set_client_info
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).
What a session is waiting for can be queried with v$session_wait.
However, with Oracle
10g, this is not nessessary anymore, as v$session_wait's information
will be exposed within v$session as well.
v$sessmetricThis view is new in Oracle 10g and allows
improved
timing and statistics.
v$session_eventThis views shows a cumulative history of events waited for
in a session. The event that a session is waiting for can be
retrieved through v$session_wait
v$enqueue_stat
can be used to break down waits on the enqueue
wait event.
The sum of all session_events in an instance is found in v$system_event.
v$session_longopsUse v$session_longops if you have a long running
pl/sql procedure and want to give feedback on how far the procedure
proceeded.
If the following Procedure is run, 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 and serial# of the session.
create table f(g number); create or replace procedure long_proc as rindex pls_integer := dbms_application_info.set_session_longops_nohint; slno pls_integer; -- Name of task op_name varchar2(64) := 'long_proc'; target pls_integer := 0; -- ie. The object being worked on context pls_integer; -- Any info sofar number; -- how far proceeded totalwork number := 1000000; -- finished when sofar=totalwork -- desc of target target_desc varchar2(32) := 'A long running procedure'; units varchar2(32) := 'inserts'; -- unit of sofar and totalwork begin dbms_application_info.set_module('long_proc',null); dbms_application_info.set_session_longops ( rindex, slno); for sofar in 0..totalwork loop insert into f values (sofar); if mod(sofar,1000) = 0 then dbms_application_info.set_session_longops ( rindex, slno, op_name, target, context, sofar, totalwork, target_desc, units); end if; end loop; end long_proc; If the procedure long_proc is run, you can issue the
following query to get feedback on its progress:
select time_remaining,sofar,elapsed_seconds from v$session_longops l, v$session s where l.sid=s.sid and l.serial# = s.serial# and s.module='long_proc' v$session_waitThis views shows what event a 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.
v$session_wait_historyThis view is new in Oracle 10g and allows
improved
timing and statistics.
v$sesstatThis view is similar to v$mystat
except that it show statistical data for all sessions.
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.
v$sgaShows 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.
v$sgastatShowing free space in the sga:
select * from v$sgastat where name = 'free memory' v$sga_dynamic_componentsInformation about SGA resize
operations since startup.
This view can also be used to find out the granule
size of SGA components.
v$sga_resize_opsv$sort_usagev$sort_segmentv$spparameterReturns the values for the spfile.
v$sqlv$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.
v$sqlareaJoin v$sqlarea's address with v$session's
sql_address.
Find the sql text of currently running sql statements:
SELECT sql_text FROM v$sqlarea where users_executing > 0; The field version_count indicates how many versions
an sql statement has.
v$sqltextv$sql_planvariable addr varchar2(20) variable hash number variable child number exec :addr := '&sqladdr'; :hash := &hashvalue; :child := &childno; select lpad(' ', 2*(level-1))||operation||' '|| decode(id, 0, 'Cost = '||position) "OPERATION", options, object_name from v$sql_plan start with (address = :addr and hash_value = :hash and child_number = :child and id=0 ) connect by prior id = parent_id and prior address = address and prior hash_value = hash_value and prior child_number = child_number order by id, position ; v$sql_text_with_newlinesThis view can be used to construct the entire Text
for each session's actual SQL Statement. Use the following statement to to
that:
Thanks to Sarmad Zafar who notified me of an error
in this PL/SQL Block.
set serveroutput on size 1000000 declare v_stmt varchar2(16000); v_sql_text v$sqltext_with_newlines.sql_text%type; v_sid v$session.sid%type; begin for r in ( select sql_text,s.sid from v$sqltext_with_newlines t, v$session s where s.sql_address=t.address order by s.sid, piece) loop v_sid := nvl(v_sid,r.sid); if v_sid <> r.sid then dbms_output.put_line(v_sid); <a href='oru_10028.html'>put_line</a>(v_stmt,100); v_sid := r.sid; v_stmt := r.sql_text; else v_stmt := v_stmt || r.sql_text; end if; end loop; dbms_output.put_line(v_sid); put_line(v_stmt,100); end; / Note: the function put_line is found here and can be used
to prevent ORU-10028.
v$sql_bind_dataJoin cursor_num with cno of v$sql_cursor.
v$sql_bind_captureNew with Oracle 10g
This view captures bind variables for all sessions and is
faster than setting 10046
on level 4.
v$sql_cursorJoin parent_handle with address of v$sql or
v$sqlarea.
v$sql_workareav$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.
v$standby_logv$statnamev$sysaux_occupantsv$sysaux_occupants doesn't exist in Oracle versions prior
to Oracle 10g.
v$sysmetricThis view is new in Oracle 10g and allows
improved
timing and statistics.
v$sysmetric_historyThis view is new in Oracle 10g and allows
improved
timing and statistics.
v$sysstatThis view is (among others) used to calculate the Hit Ratio.
v$system_eventThis view gathers information about total wait times for events. timed_statistics
must be set to true in order to gather the timing information.
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.
This view totals all events in an instance. v$session_event
breaks it down to a session level.
v$undostatv$tempstatv$threadThe Oracle SID can be retrieved through select instance
from v$thread
v$timerv$transactionImportant 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 ##A(v$rollname,#rollname). This is demonstrated in Transactions
generate undo
v$timezone_namesv$transportable_platformWhich platforms are supported for cross platform transportable
tablespaces.
v$versionUse this view to find out what version you actually work
on:
select * from v$version; BANNER ---------------------------------------------------------------- Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production PL/SQL Release 8.1.7.0.0 - Production CORE 8.1.7.0.0 Production TNS for 32-bit Windows: Version 8.1.7.0.0 - Production NLSRTL Version 3.4.1.0.0 - Production v$waitstattotal_waits where event='buffer
busy waits' is equal the sum of count in v$system_event.
MiscThanks to Elizabeth Seager who made me aware of an
error on this page. |