oracle常用的動態檢視

Oracle's V$ Views

v$bh

This dynamic view has an entry for each block in the .

The column status can be:

• free
  This block is not in use
• xcur
  Block held exclusively by this
• 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$db_object_cache

This view displays objects that are cached (pinned) in the . See also .

v$event_name

Contains a record for each .

v$lock

This view stores all information relating to locks in the database. The interesting columns in this view are sid (identifying the 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 . 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:

• 1: ,
• 2: ,
• 3: ,
• 4: ,
• 5: and
• 6:

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 is being refreshed.

A more detailed example can be found

See also .

v$locked_object

Who is locking what:

select

oracle_username

os_user_name,

locked_mode,

object_name,

object_type

from

v$locked_object a, b

where

a.object_id = b.object_id

v$log

Contains information on each log group. See also .

Comman values for the status column 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
• 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$log_history

This view contains an entry for each that occured. The column first_time indicates the time of the first entry???

On , this view .

v$mystat

This view records statistical data about the session that accesses it. Join statistic# with .
is also similar to , except that v$sysstat accumulates the statistics as soon as a session terminates.

See also .

v$nls_parameters

The 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

This view can be used to obtain valid values for NLS parameters such as

• supported character sets
• languages
• territories
• sorting orders

v$parameter

Lists the name-value pairs of the 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 = ''

The columns isses_modifiable and issys_modifiable can be used to determine if a parameter can be changed at session level using or at system level using . 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 option must be used.

There are also some undocumented (or hidden?) parameters.

v$process

Join v$process's addr with paddr.

The column traceid is equal to the value used in alter session set .

v$session

The column audsid can be joined with sys_context('userenv','SESSIONID') to find out which session is the "own one". Alternatively, can be used.

The fields module and action of v$session can be set with . (See for an example.

The field client_info can be set with

Join sid with 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 (alter system kill session).

A client can set some information in client_info. For example, related sessions can be found with

.... where client_info 'rman%';

What a session is waiting for can be queried with . However, with , this is not nessessary anymore, as v$session_wait's information will be exposed within v$session as well.

See also .

v$session_event

This views is similar to . However, it breaks it down to currently connected .

v$session_event has also the column max_wait that shows the maximum time waited for a .

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 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 each session is waiting for, or what the last event was that it waited for.
In contrast, lists the cumulative history of events waited for in a session.

The columns P1, P2 and P3 are that are dependant on the event. With , v$session_wait's information will be exposed within as well.

Since 10g, Oracle displays the v$session_wait information also in the view.

v$session_wait_history

This view is new in and allows .

v$sesstat

This view is similar to except that it shows cumulated statistics for all sessions.

Join sid with and join statistic# with .

v$sesstat is also similar to , except that v$sysstat accumulates the statistics as soon as a session terminates.

v$sga

Shows how much memory the uses. Select * from v$sga is roughly the same as typing in with the exeption that the latter also show the total.

v$sgastat

Showing free space in the :

select * from v$sgastat where name = 'free memory'

v$sql

v$sql is similar to , the main difference being that v$sql drills down to select * from whereas v$sqlarea drills down to select sum from x$kglob. See also .

v$sqlarea

Join v$sqlarea's address with v$session's sql_address.

Find the SQL-text of currently running :

select sql_text from v$sqlarea where users_executing > 0;

The field version_count indicates how many an sql statement has.

v$sql_plan

addr varchar2(20)

hash number

child number

exec :addr := '&sqladdr'; :hash := &hashvalue; :child := &childno;

(' ', 2*(level-1))||operation||' '||

(id, 0, 'Cost = '||position) "OPERATION",

options, object_name

from v$sql_plan

(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:

select sql_text,address,hash_value,child_number from where users_executing > 0;

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:

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);

put_line(v_stmt,100);

v_sid := r.sid;

v_stmt := r.sql_text;

else

v_stmt := v_stmt || r.sql_text;

end if;

end loop;

.put_line(v_sid);

dbms_output.put_line(v_stmt,100);

end;

/

v$sql_cursor

Join parent_handle with address of v$sql or v$sqlarea.

v$sql_workarea

v$sql_workarea can be joined with on address and hash_value, and it can be joined with on address, hash_value and child_number.

v$sysstat

v$sysstat is similar to . 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 (name = 'CPU used by this session')

This view is (among others) used to calculate the .

v$system_event

This view displays the count (total_waits) of all since of the .

If is set to true, the sum of the for all 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='' is equal the sum of count in .

can be used to break down waits on the wait event.

While this view totals all events in an instance, breaks it down to all currently connected .

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 . This is demonstrated in

v$waitstat

total_waits where event='' is equal the sum of count in .