Re: How to identify the SQL being rolled back?
- From: Jared Still <jkstill@xxxxxxxxx>
- To: Brandon.Allen@xxxxxxxxxxx
- Date: Fri, 26 Aug 2005 20:13:02 +0100
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@xxxxxxxxxxx> 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
Other related posts:
