• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » How to monitor transaction Rollback Progress

How to monitor transaction Rollback Progress

August 21, 2021 by techgoeasy Leave a Comment

I had a situation where I had to killed the long-running session in my Oracle database. The session does not terminate immediately. It is marked for the kill and the rollback is done in the background.

SQL> ALTER SYSTEM KILL SESSION ’12,2233';

ALTER SYSTEM KILL SESSION ’12,2233'
*
ERROR at line 1:
ORA-00031: session marked for kill

SQL> select username, status from v$session where SID=12;

USERNAME    STATUS
——————–      ——–
SYS         KILLED

So how do we track, whether background rollback is in progress or not?

We can query the dynamic view v$transaction. Column used_ublk will be updated frequently. The value will keep on decreasing.  Once the value becomes 0 or no row is returned from this view rollback is complete and entry will be removed from v$session.

select t.used_ublk,s.sid,s.username from v$transaction t, v$session s where s.taddr = t.addr and
s.sid = '12';
-----------
75875

select t.used_ublk,s.sid,s.username from v$transaction t, v$session s where s.taddr = t.addr and
s.sid = '12';
--------
75812

Based on this value, we can calculate the time of rollback easily. Once the rollback is completed, session will be completely gone

or we can use the below query

alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
SELECT usn,
state,
undoblockstotal "Total",
undoblocksdone "Done",
undoblockstotal - undoblocksdone "ToDo",
DECODE (
cputime,
0, 'unknown',
SYSDATE( ( (undoblockstotal - undoblocksdone)
/ (undoblocksdone / cputime))
/ 86400))
"Estimated time to complete"
FROM v$fast_start_transactions;

Filed Under: Oracle, Oracle Database Tagged With: undo kill

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to use sed to remove comments and blank lines
  • How to recover database using RMAN
  • How to check Stale statistics
  • Java web start(JWS) in R12
  • How to delete the archive logs in Oracle

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us