qundo.sql

COLUMN sid_serial FORMAT A20
COLUMN username FORMAT A20
COLUMN program FORMAT A30
COLUMN undoseg FORMAT A20
COLUMN undo FORMAT A20
SET LINESIZE 120

SELECT TO_CHAR(s.sid)||’,’||TO_CHAR(s.serial#) AS sid_serial,
NVL(s.username, ‘(oracle)’) AS username,
s.program,
r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||’K’ AS undo
FROM v$rollname r,
v$session s,
v$transaction t,
v$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.name = ‘db_block_size’;

select sum(bytes)/1024/1024 “available M in UNDO” from dba_undo_extents where status=’EXPIRED’;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s