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
v$archive_dest_status
This view allows to find status and errors for each of the defined
v$archived_log
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).
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.
v$controlfile
v$controlfile_record_section
v$bh
The column statuscan 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
v$buffer_pool
v$buffer_pool_statistics
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:
LOG_MODE
------------
ARCHIVELOG
switchover_status: can be used to determine if it is possible to perform a switchover operation Only available for physical standby databases. Can be:
- NOT ALLOWED,
- SESSIONS ACTIVE,
- SWITCHOVER PENDING,
- SWITCHOVER LATENT,
- TO PRIMARY,
- TO STANDBY or
- RECOVERY NEEDED.
database_role determines if a database is a primary or a logical standby database or a physical standby database.
v$datafile
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_header
Various 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_status
v$db_object_cache
This view displays objects that are cached (pinned) in the library cache. See also dbms_shared_pool.
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.
For a list of enqueue types, refer to enqueue types in x$ksqst.
v$eventmetric
v$event_name
v$filemetric
v$filestat
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:
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_definition
Contains the defintion in its attribute view_definition for the views of v$fixed_table.
v$flash_recovery_area_usage
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.
v$instance_recovery
v$latch
Oracle collects statistics for the activity of all latches and stores these in this view. Getsis 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$librarycache
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/requestpair.
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 requestis other than 0 or 1. The possible values for lmode and request are:
- 1: null,
- 2: Row Share (SS),
- 3: Row Exclusive (SX),
- 4: Share (S),
- 5: Share Row Exclusive (SSX) and
- 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
v$locked_object
Who is locking what:
select
oracle_username
os_user_name,
locked_mode,
object_name,
object_type
from
where
a.object_id = b.object_id
v$log
Comman values for the statuscolumn are:
- UNUSED:
Oracle8 has never written to this group, - CURRENT:
This is the active group. - ACTIVE:
Oracle has written to this log before, it is needed for instance recovery.
The active log is the one with the current log sequence number - INACTIVE:
Oracle has written to this log before; it is not needed for instance recovery.
v$logfile
This 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_contents
v$log_history
This view contains an entry for each Log Switch that occured. The column first_timeindicates the time of the first entry???
v$logstdby
Can be used to verify that archived redo logs are being applied to standby databases.
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.
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$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.
v$sesstat is also similar to v$sysstat, except that v$sysstat accumulates the statistics as soon as a session terminates.
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.
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
v$object_usage
v$object_usage gathers information about used (accessed) indexes when an index is monitored using alter index ... monitoring usage.
v$open_cursor
v$option
v$parameter
Lists 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_modifiableand 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.
v$pgastat
Thanks to Olegwho notified me of a typo (v$pgastat instead of v$pga_stat).
v$process
The column traceidis equal to the value used in alter session set .
v$pwfile_users
Lists all users who have been granted sysdba or sysoper privileges. See adding user to a password file.
v$recover_file
v$recovery_file_dest
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).
Until 10g, the view only consist of two columns: keyword and length. From 10gR2 onwards, it has also the columns reserved, res_type, res_attr, res_semiand duplicate. Each of these new columns can only be either 'Y' (meaning: yes) or 'N' (meaning: no)
v$resource_limit
v$rollname
The 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$rollstat
Statistics for rollback segements
v$session
The column audsidcan 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 moduleand action of v$session can be set with dbms_application_info.set_module. (See v$session_longops for an example.
A record in v$session contains sid and serial#. These numbers can be used kill a session (alter system kill session).
A client can set some information in client_info. For example, RMAN related sessions can be found with
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$sessmetric
v$session_event
This views is similar to v$system_event. However, it breaks it down to currently connected sessions.
v$session_longops
Use v$session_longopsif 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_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.
In contrast, v$session_event lists the cumulative history of events waited for in a session.
The columns P1, P2and 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_history
v$sesstat
v$sesstat is also similar to v$sysstat, except that v$sysstat accumulates the statistics as soon as a session terminates.
v$sga
Shows how much memory the shared global area uses. Selecting * from v$sgais roughly the same as typing show sga in sql plus with the exeption that the latter also show the total.
v$sgastat
select * from v$sgastat where name = 'free memory'
v$sga_dynamic_components
v$sga_resize_ops
v$sort_usage
v$sort_segment
v$spparameter
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.
v$sqlarea
Join v$sqlarea's addresswith v$session's sql_address.
select sql_text from v$sqlarea where users_executing > 0;
v$sqltext
v$sql_plan
variable addr varchar2(20)
variable hash number
variable child number
exec :addr := '&sqladdr'; :hash := &hashvalue; :child := &childno;
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 ;
In order to find valid values for sqladdr, hashvalue and childno, this SQL statement can be used:
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:
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_stmt,100);
end;
/
Thanks to Sarmad Zafar who notified me of an error in this PL/SQL Block.
v$sql_bind_data
Join cursor_numwith cno of v$sql_cursor.
v$sql_bind_capture
v$sql_cursor
Join parent_handlewith address of v$sql or v$sqlarea.
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.
v$standby_log
v$statname
v$sysaux_occupants
v$sysmetric
v$sysmetric_history
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.
v$system_event
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.
While this view totals all events in an instance, v$session_event breaks it down to all currently connected sessions.
v$undostat
v$tempfile
v$tempseg_usage
v$tempstat
v$thread
The Oracle SID can be retrieved through select instance from v$thread
v$timer
This view has only one column (hsecs) which counts hundreths of seconds. Whenever it overflows four bytes, it starts again with 0.
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
v$timezone_names
v$transportable_platform
v$version
Use 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$waitstat
total_waits where event='buffer busy waits' is equal the sum of countin v$system_event
No comments:
Post a Comment