Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!news4.google.com!border1.nntp.dca.giganews.com!nntp.giganews.com!newscon06.news.prodigy.com!prodigy.net!newsmst01b.news.prodigy.com!prodigy.com!postmaster.news.prodigy.com!newssvr30.news.prodigy.com.POSTED!a25fe021!not-for-mail
From: Mladen Gogala <gogala@sbcglobal.net>
Subject: Re: truncate /*+append*/
User-Agent: Pan/0.14.2.91 (As She Crawled Across the Table)
Message-Id: <pan.2006.03.04.03.42.46.527998@sbcglobal.net>
Newsgroups: comp.databases.oracle.server
References: <1141328588.206530.61630@p10g2000cwp.googlegroups.com> <pan.2006.03.02.20.06.01.690463@telus.net> <1141331016.239208.326430@i40g2000cwc.googlegroups.com> <du94ea$a6r$1@news.BelWue.DE> <pan.2006.03.03.14.12.22.656863@sbcglobal.net> <1141397607.589869.269980@p10g2000cwp.googlegroups.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Lines: 37
NNTP-Posting-Host: 69.177.91.154
X-Complaints-To: abuse@prodigy.net
X-Trace: newssvr30.news.prodigy.com 1141443767 ST000 69.177.91.154 (Fri, 03 Mar 2006 22:42:47 EST)
NNTP-Posting-Date: Fri, 03 Mar 2006 22:42:47 EST
Organization: SBC http://yahoo.sbc.com
X-UserInfo1: Q[RGW][DRJVKRVH]]RKB_UDAZZ\DPCPDLXUNNHXIJYWZUYICD^RAQBKZQTZTX\_I[^G_KGFNON[ZOE_AZNVO^\XGGNTCIRPIJH[@RQKBXLRZ@CD^HKANYVW@RLGEZEJN@\_WZJBNZYYKVIOR]T]MNMG_Z[YVWSCH_Q[GPC_A@CARQVXDSDA^M]@DRVUM@RBM
Date: Sat, 04 Mar 2006 03:42:47 GMT
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:262778

On Fri, 03 Mar 2006 06:53:27 -0800, cpiodumpv wrote:

> Excellent response if there is undo I couldnt' wrap my mind around what
> the undo was.
> 
> I agree. In the future I should not quote experts but prove these
> things to myself.
> 
> Regards

Good way to measure it is to look into V$TRANSACTION and observe the
USED_UBLK column. This column reports the number of UNDO blocks used
by the particular transaction. You can join V$TRANSACTION to V$SESSION
which has TADDR column to get even more information. My favorite
join goes something like this:

SELECT sess.username,
  sess.sid,
  sess.serial# serial,
  p.spid "System PID",
  sess.PROGRAM,
  sess.osuser,
  sess.machine,
  t.used_ublk "Undo blocks",
  t.status "Trans. Status",
  to_char(logon_time,   ' MM/DD/YYYY HH24:MI') "Logged In"
FROM v$session sess,
  v$transaction t,
  v$process p
WHERE sess.saddr = t.ses_addr
 AND sess.paddr = p.addr
ORDER BY t.used_ublk DESC

This was formatted by SQL*Developer (formerly Raptor)
-- 
http://www.mgogala.com