Monitoring UNDO Tips
Oracle Database Tips by Donald BurlesonFebruary 21, 2015
do I monitor the undo logs in Oracle. I need large
UNDO logs for flashback.
Oracle Undo segments support read consistent operations (and
other database operations such as flashback operations).
Also see my notes on how to
Oracle creates an undo segment in the SYSTEM tablespace when
the database is created. This undo segment only supports
operations in the SYSTEM tablespace. This script will
monitor UNDO usage changes:
substr(a.os_user_name,1,15) "OS User",
"# of Records",
see code depot for full script
a.object_id = b.object_id
a.xidusn = c.segment_id
a.xidusn = d.xidusn
a.xidslot = d.xidslot
d.addr = e.taddr;
So, where do we get new undo
segments? We create our undo tablespace with the create undo
tablespace command (we will talk about tablespaces in more
detail next) and Oracle will create the undo segments and
manage the for us automatically.
This script also monitors UNDO
operations using v$session and v$transaction:
ses.saddr = tra.ses_addr;
In Oracle, monitoring UNDO size can be
controlled with the undo_retention parameter, and the
setting for UNDO are determined by the level of DML activity
in the database. This script will monitor UNDO size
changes. Note that this script monitors UNDO by using
the v$undostat view:
d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
UNDO SIZE [MByte]"
SEE CODE DEPOT FOR FULL SCRIPTS
c.contents = 'UNDO'
c.status = 'ONLINE'
a.ts# = b.ts#
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size';
Also, note that the automatic undo
management feature introduced in Oracle 9i has been improved
with the following capabilities.
Some of the undo
management related initialization parameters are eliminated
(starting in Oracle 10g) because the UNDO size is
automatically calculated. These obselete UNDO monitoring
parameters include max_rollback_segment, row_locking,
undo_suppress_errors, serializable, and
This script is useful in determining a
HWM for sessions, for UNDO sizing:
rem session.sql - displays all connected sessions
For complete scripts to monitor UNDO,
set echo off;
set termout on;
set linesize 80;
set pagesize 60;
set newpage 0;
a.name = 'logons cumulative'
set heading off;
select 'Sessions on database '||substr(name,1,8) from
set heading on;
SEE CODE DEPOT FOR
b.paddr = a.addr
order by spid;
set heading off;
select 'To kill, enter
SQLPLUS> ALTER SYSTEM KILL SESSION',
SER#'||''''||';' from dual;
Oracle Training from Don Burleson
The best on site
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
Burleson is the American Team
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
is the registered trademark of Oracle Corporation.