Re: How to identify the SQL being rolled back?

From: Jared Still <jkstill_at_gmail.com>
Date: Fri, 26 Aug 2005 20:13:02 +0100
Message-ID: <bf4638050826121361611c53@mail.gmail.com>

You might try the script below.

Look for decreasing number of rbs records.

Jared

set line 140

col osuser format a8 heading 'O/S|User'
col username format a10 heading 'Oracle|Userid' col sid format 9999 head 'SID'
col segment_name format a6 heading 'R-S|Name' col space format a5 head 'Space|Trans'
col recursive format a5 head 'Recur|sive|Trans' col noundo format a5 head 'No|Undo'
col used_ublk format 999,999,999 head 'Used|Rbs|Blks' col used_urec format 999,999,999 head 'Used|RBS|Recs' col log_io format 9,999,999,999 head 'Logical|IO Blks' col phy_io format 9,999,999,999 head 'Physical|IO Blks' col txt format a30 heading 'Current|Statement' word

--spool showtrans.txt

select s.osuser

,s.username
,s.sid
,r.segment_name
,t.space
,t.recursive
,t.noundo
,t.used_ublk
,t.used_urec
,t.log_io
,t.phy_io
,substr(sa.sql_text,1,200) txt

from v$session s,
v$transaction t,
dba_rollback_segs r,
v$sql sa
where s.saddr=t.ses_addr
and t.xidusn=r.segment_id(+)
and s.sql_address=sa.address(+)
/

On 8/26/05, Allen, Brandon <Brandon.Allen_at_oneneck.com> wrote:
>
> I have a huge transaction rolling back in a 9.2.0.6 <http://9.2.0.6>database and am curious what it was. From my calculations based on
> v$transaction.used_ublk, it looks like it is going to take 4 hours to
> complete the rollback. All I see in v$sql.sql_text is "ROLLBACK". Any idea
> where/how I can find more detail about what is being rolled back - either
> the SQL statement(s), or the affected objects?
> Thanks,
> Brandon
>
> Privileged/Confidential Information may be contained in this message or
> attachments hereto. Please advise immediately if you or your employer do not
> consent to Internet email for messages of this kind. Opinions, conclusions
> and other information in this message that do not relate to the official
> business of this company shall be understood as neither given nor endorsed
> by it.
>

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Aug 26 2005 - 14:15:10 CDT