Re: Interpreting USED_UREC from V$TRANSACTION
From: GG <grzegorzof_at_interia.pl>
Date: Fri, 06 Jul 2012 20:02:28 +0200
Message-ID: <4FF72834.9080704_at_interia.pl>
W dniu 2012-07-06 19:20, Herring Dave - dherri pisze:
> Folks,
> I'm monitoring a VERY large rollback operation and could use some help in understanding conflicting values for USED_UREC in V$TRANSACTION.
>
> This is for 11.2.0.3 on RHEL 5.6. The transaction was a DROP COLUMN against a 1.3 TB table which I believe ran out of undo after 50+ hrs. The SQL was generated from a data modeling tool and even after warning about runtimes they wanted the command run exactly as generated.
>
> Now that the DROP COLUMN command has been rolling back for 5 days, I'm getting pretty confused on what V$TRANSACTION is telling me, specifically USED_UREC. Periodically over the last few days I've been querying this view and the values decreased way below zero and are now back about 800 million. In all cases each check a little later showed the values were decreasing so I knew it was still rolling back:
>
Hi,
try with this still great script :
http://www.ixora.com.au/scripts/sql/9.0/rolling_back.sql
Date: Fri, 06 Jul 2012 20:02:28 +0200
Message-ID: <4FF72834.9080704_at_interia.pl>
W dniu 2012-07-06 19:20, Herring Dave - dherri pisze:
> Folks,
> I'm monitoring a VERY large rollback operation and could use some help in understanding conflicting values for USED_UREC in V$TRANSACTION.
>
> This is for 11.2.0.3 on RHEL 5.6. The transaction was a DROP COLUMN against a 1.3 TB table which I believe ran out of undo after 50+ hrs. The SQL was generated from a data modeling tool and even after warning about runtimes they wanted the command run exactly as generated.
>
> Now that the DROP COLUMN command has been rolling back for 5 days, I'm getting pretty confused on what V$TRANSACTION is telling me, specifically USED_UREC. Periodically over the last few days I've been querying this view and the values decreased way below zero and are now back about 800 million. In all cases each check a little later showed the values were decreasing so I knew it was still rolling back:
>
Hi,
try with this still great script :
http://www.ixora.com.au/scripts/sql/9.0/rolling_back.sql
but If You are still not in last loop this maybe missleading :( .
Regards
GregG
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jul 06 2012 - 13:02:28 CDT