> The script included will produce the following output. The key for you
> would be to watch the "#Of Undo Blks Used" column. If it's incrementing
> the updates, etc. are in progress. If it's decreasing, the transaction is
> being rolled back.
>
> Hope this helps.
>
>
> Wed Oct 24
> page 1
> ***** ROLLBACK SEGMENTS WITH ACTIVE
> TRANSACTIONS ****
>
> Cur Cur
> #Of #Of
> Init Next Opt High Cur Ext# Blk#
> Ora Undo KB Of Undo
> RollBack Size Size #Of Size Size Size Used #Of #Of Being Being
> Ses Transaction Strt Strt Blks Undo Recs
> Name Meg Meg Ext Meg Meg Meg Meg %Used Srk Trn Used Used
> ID Start_Time Ext# Blk# Used Used Used
> --------- ----- ----- --- ---- ------ ---- ----- ----- --- --- ----- -----
> ---- ------------------ ---- ------- ----- ------ -------
> R01 10.0 10.0 10 100 100.0 100 1 1 0 1 0 478
> 171 10/24/01 08:11:26 0 284 148 1184 12206
> R02 10.0 10.0 10 100 100.0 100 0 0 7 368
> R03 10.0 10.0 10 100 100.0 100 0 0 1 586
> R04 10.0 10.0 10 100 100.0 100 0 0 4 709
> R05 10.0 10.0 10 100 100.0 100 0 0 9 331
> R06 10.0 10.0 10 100 100.0 100 0 0 4 1083
> R07 10.0 10.0 10 100 100.0 100 0 0 4 891
> R08 10.0 10.0 10 100 100.0 100 0 0 0 0 2 502
> R09 10.0 10.0 10 100 100.0 100 0 0 4 854
> R10 10.0 10.0 10 100 100.0 100 0 0 7 736
> SYSTEM .1 .1 61 5 5 0 0 36 0
>
> Wed Oct 24
> page 1
> ***** USERS WITH ACTIVE
> TRANSACTIONS ****
>
> Oracle Pgm Oracle Oracle
> Unix User Unix Unix Session
> Rollback
> User Name Pid Pid ID Serial# TTY# Program Name
> Status Segment
> ---------- ---------- ------ ------ ------- ------- -------
> ------------------------------------------------ --------- ----------
> Current SQL Statement
> --------------------------------------------------------------------------
> ----------------------------------------------------------
> bart marge 10373 10374 171 38791 sqlplus_at_homer
> (TNS V1-V3) ACTIVE R01
> SELECT COUNT(*) FROM STG_BOOK_INV WHERE ISBN = :b1
>
>
>
>
>
> REM ================================ START OF FILE
> ===============================
> set verify off
> set pagesize 36
> set linesize 132
> set pause on
> set pause 'Hit enter to continue'
> set feedback off
> set showmode off
> set echo off
>
> ttitle '***** ROLLBACK SEGMENTS WITH ACTIVE TRANSACTIONS ****'
> col owner heading 'Owner' format a6
> col segment_name heading 'RollBack|Name' format a9
> col tablespace_name heading 'TableSpace' format a12
> col EXTENTS heading '#Of|Ext' format 99
> col SM heading 'Size|Meg' format 999
> col IE heading 'Init|Size|Meg' format 99.9
> col NE heading 'Next|Size|Meg' format 99.9
> col OPT heading "Opt|Size|Meg" format 999.9
> col HIGH heading "High|Size|Meg" format 999
> col SHRINKS heading "#Of|Srk" format 99
> col TRANS heading "Cur|#Of|Trn" format 99
> col STATUS heading 'Current|Status' format a9
> col curext heading 'Cur|Ext#|Being|Used' format 9990
> col curblk heading 'Cur|Blk#|Being|Used' format 9990
> col ef heading 'Ora|Ses|ID' format 990
> col start_time heading 'Transaction|Start_Time' format a18
> col start_uext heading 'Strt|Ext#' format 990
> col start_ubablk heading 'Strt|Blk#' format 999990
> col used_ublk heading '#Of|Undo|Blks|Used' format 9990
> col used_urec heading '#Of|Undo|Recs|Used' format 999990
> col SUK heading 'KB Of|Undo|Used' format 99990
> col SMU heading 'Used|Meg' format 9990
> col SMUP heading '%Used' format 999
> break on owner on segment_name on tablespace_name on IE on NE on EXTENTS
> on SM on OPT on HIGH -
> on SMU on SMUP on SHRINKS on TRANS on curext on curblk
> select ds.segment_name segment_name,
> drs.initial_extent / 1048576 IE,
> drs.next_extent / 1048576 NE,
> ds.extents EXTENTS,
> (ds.blocks * (vp.value / 1024)) / 1024 SM,
> vr.optsize / 1048576 OPT,
> vr.hwmsize / 1048576 HIGH,
> (vtss.s_used_ublk * (vp.value / 1024)) / 1024 SMU,
> (vtss.s_used_ublk / ds.blocks) * 100 SMUP,
> vr.shrinks SHRINKS,
> vr.xacts TRANS,
> vr.curext,
> vr.curblk,
> vts.sid ef,
> vts.start_time,
> vts.start_uext,
> vts.start_ubablk,
> vts.used_ublk,
> (vts.used_ublk * (vp.value / 1024)) SUK,
> vts.used_urec
> from v$parameter vp, v$rollstat vr, dba_segments ds, dba_rollback_segs
> drs,
> ( select vt.xidusn,
> vs.sid,
> vt.start_time,
> vt.start_uext,
> vt.start_ubablk,
> vt.used_ublk,
> vt.used_urec
> from v$transaction vt,
> v$session vs
> where vt.addr = vs.taddr
> ) vts,
> ( select vt.xidusn,
> sum(vt.used_ublk) s_used_ublk,
> sum(vt.used_urec) s_used_urec
> from v$transaction vt
> group by vt.xidusn
> ) vtss
> where ds.segment_type = 'ROLLBACK'
> and ds.segment_name = drs.segment_name
> and drs.segment_id = vr.usn
> and drs.segment_id = vts.xidusn (+)
> and drs.segment_id = vtss.xidusn (+)
> and vp.name = 'db_block_size'
> order by ds.segment_name,
> drs.initial_extent / 1048576,
> drs.next_extent / 1048576,
> ds.extents,
> (ds.blocks * (vp.value / 1024)) / 1024,
> vr.optsize / 1048576,
> vr.hwmsize / 1048576,
> (vtss.s_used_ublk * (vp.value / 1024)) / 1024,
> (vtss.s_used_ublk / ds.blocks) * 100,
> vr.shrinks,
> vr.xacts,
> vr.curext,
> vr.curblk;
>
> prompt
>
> ttitle '***** USERS WITH ACTIVE TRANSACTIONS ****'
> col bb heading "Unix|User" format a10
> col cc heading "Oracle|User|Name" format a10
> col dd heading "Pgm|Unix|Pid" format a6
> col ee heading "Oracle|Unix|Pid" format a6
> col ef heading "Oracle|Session|ID" format 999999
> col eg heading "Serial#" format 999999
> col ff heading "TTY#" format a7
> col gg heading "Program Name" format a48
> col hh heading "Server|Type" format a9
> col ii heading "Status" format a9
> col jj heading "Rollback|Segment" format a10
> col kk heading "Current SQL Statement" format a160
> select unique
> vs.osuser bb,
> vs.username cc,
> vs.process dd,
> vp.spid ee,
> vs.sid ef,
> vs.serial# eg,
> vs.terminal ff,
> vs.program gg,
> vs.status ii,
> vr.name jj,
> vsql.sql_text kk
> from v$rollname vr,
> v$transaction vt,
> v$sql vsql,
> v$process vp,
> v$session vs
> where vs.paddr = vp.addr
> and vs.sql_address = vsql.address (+)
> and vs.sql_hash_value = vsql.hash_value (+)
> and vs.taddr = vt.addr
> and vt.xidusn = vr.usn;
>
> exit;
> REM ================================ END OF FILE
> ===============================
>
>
>
>
> -----Original Message-----
> From: Lau, John [mailto:john.lau_at_csfb.com]
> Sent: Wednesday, October 24, 2001 9:00 AM
> To: Multiple recipients of list ORACLE-L
> Subject: How to detect transactions being rolled back?
>
>
> Is there a way to detect if a transaction is currently being rolled back?
> ie. If it fails part way thru or is cancelled by the user?
>
> Thanks in advance,
>
> John
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Brian MacLean
INET: bmaclean_at_vcommerce.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Wed Oct 24 2001 - 17:08:44 CDT
