Posts tagged: Query to find the remaining time for transaction rollback

May 20 2010

How to find out how much rollback a session has to do

select time_remaining
from v$session_longops
where sid =<sid of the session doing the rollback>;

Usage of v$session_longops:
===========================
SQL> desc v$session_longops;
SID NUMBER Session identifier
SERIAL# NUMBER Session serial number
OPNAME VARCHAR2(64) Brief description of the operation
TARGET VARCHAR2(64) The object on which the operation is carried out
TARGET_DESC VARCHAR2(32) Description of the target
SOFAR NUMBER The units of work done so far
TOTALWORK NUMBER The total units of work
UNITS VARCHAR2(32) The units of measurement
START_TIME DATE The starting time of operation
LAST_UPDATE_TIME DATE Time when statistics last updated
TIMESTAMP DATE Timestamp
TIME_REMAINING NUMBER Estimate (in seconds) of time remaining for theoperation to complete
ELAPSED_SECONDS NUMBER The number of elapsed seconds from the start of operations
CONTEXT NUMBER Context
MESSAGE VARCHAR2(512) Statistics summary message
USERNAME VARCHAR2(30) User ID of the user performing the operation
SQL_ADDRESS RAW(4  |  8) Used with the value of the SQL_HASH_VALUE column to identify the SQL statement associated with the operation
SQL_HASH_VALUE NUMBER Used with the value of the SQL_ADDRESS column to identify the SQL statement associated with the operation
SQL_ID VARCHAR2(13) SQL identifier of the SQL statement associated withthe operation QCSID NUMBER Session identifier of the parallel coordinator

This view displays the status of various operations that run for longer than 6seconds (in absolute time). These operations currentlyinclude many backup and recovery functions, statistics gathering, and queryexecution, and more operations are added for every Oracle release.

Alibi3col theme by Themocracy