As an example, if you killed a session which was supposed to update/delete 1Million rows on half the way , it will definitely take some time to roll the changes back. This can be verified by checking the v$transaction table.
SQL> select count(*) cnt from test_tab1;
CNT
----------
769538
SQL> update test_tab1 set c1 = c1+1;
Huge update is going on now.In between, if you kill the session and check the v$transaction table you will see the change in the values of "Number of undo blocks used" and "Number of undo records used"
SQL> alter system kill session '49,27133';
alter system kill session '49,27133'
*
ERROR at line 1:
ORA-00031: session marked for kill
SQL> select status
2 from v$session
3 where sid = 49;
STATUS
--------
KILLED
SQL> select used_ublk,used_urec
2 from v$transaction
3 where ses_addr=(
4 select saddr
5 from v$session
6 where sid = 49
7 );
USED_UBLK USED_UREC
---------- ----------
24769 161040
SQL> /
USED_UBLK USED_UREC
---------- ----------
24081 155939
SQL> /
USED_UBLK USED_UREC
---------- ----------
22605 145321
SQL> /
USED_UBLK USED_UREC
---------- ----------
10487 66038
SQL> /
USED_UBLK USED_UREC
---------- ----------
6637 41384
SQL> /
no rows selected
The rollback is over. We can check V$SESSION now.
SQL> select status
2 from v$session
3 where sid = 49;
no rows selected
No comments:
Post a Comment