Re: truncate /*+append*/

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Sat, 04 Mar 2006 03:42:47 GMT
Message-Id: <pan.2006.03.04.03.42.46.527998@sbcglobal.net>

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

Received on Fri Mar 03 2006 - 21:42:47 CST