| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Following the rollback of a killed session
Hi,
When a session executes a rollback statement, you can follow the rollback
by watching the used_blk column in v$transaction.
But when the session is killed, taddr in v$session is "set" to null and the row in the
v$transaction is deleted.
So, how can you follow the rollback executing for this session?
In addition, who executes this rollback? The server process (dedicated or
shared) or a background process?
Here's a test made on my laptop (Windows NT4, Oracle 8iR3). There are 2 sessions (TEST and SYSTEM). I set "time on" to follow the two sessions.
Session 1: TEST
20:03:13 TEST>update guichets set c_code_postal='00000';
26295 rows updated.
Elapsed: 00:01:39.52
20:04:52 TEST>
20:04:53 TEST>rollback;
Rollback complete.
Elapsed: 00:01:00.47
20:05:54 TEST>
20:06:11 TEST>update guichets set c_code_postal='00000';
26295 rows updated.
Elapsed: 00:01:18.53
20:07:34 TEST>
20:07:59 TEST>select * from dual;
select * from dual
*
ERROR at line 1:
ORA-00028: your session has been killed
Elapsed: 00:00:00.71
20:08:05 TEST>
Session 2: SYSTEM
20:07:43 SYSTEM>select s.sid, s.username, 20:07:43 2 t.addr tx, t.start_time, t.status, t.start_scnb, 20:07:43 3 decode(t.noundo, 'YES', 'No RB', nvl(r.name, 'No RB')) rollback, 20:07:43 4 round(rs.rssize/1024) rssize,20:07:43 5
20:07:43 6 from v$rollstat rs, v$rollname r, v$session s, v$transaction t
20:07:43 7 where r.usn (+) = t.xidusn
20:07:43 8 and rs.usn (+) = t.xidusn
20:07:43 9 and s.saddr = t.ses_addr
20:07:44 10 order by 7, 6, 1
20:07:44 11 /
Sid Utilisateur Tx Addr Tx Start Time Tx Status Tx
SCN Rollback Taille (Ko) RB?
------- --------------- -------- -------------------- ----------------
----------- ----------- ------------ ---
11 TEST 02BF2448 07/21/03 20:06:12 ACTIVE
6449420 RS_1 2,516
1 row selected.
Elapsed: 00:00:00.00
20:07:44 SYSTEM>select sid, serial#, username, taddr from
v$session where sid=11;
Sid SERIAL# Utilisateur TADDR ------- ---------- --------------- --------
11 142 TEST 02BF2448 1 row selected.
Elapsed: 00:00:00.10
20:07:44 SYSTEM>select * from v$transaction;
ADDR XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN
UBAREC Tx Status
-------- ---------- ---------- ---------- ---------- ---------- ----------
---------- ----------------
Tx Start Time Tx SCN START_SCNW START_UEXT START_UBAFIL START_UBABLK
START_UBASQN START_UBAREC SES_ADDR
-------------------- ----------- ---------- ---------- ------------ ------------
1 row selected.
Elapsed: 00:00:00.30
20:07:45 SYSTEM>alter system kill session '11,142';
System altered.
Elapsed: 00:00:00.80
20:07:45 SYSTEM>select s.sid, s.username, 20:07:45 2 t.addr tx, t.start_time, t.status, t.start_scnb, 20:07:46 3 decode(t.noundo, 'YES', 'No RB', nvl(r.name, 'No RB')) rollback, 20:07:47 4 round(rs.rssize/1024) rssize,20:07:47 5
20:07:48 6 from v$rollstat rs, v$rollname r, v$session s, v$transaction t 20:07:49 7 where r.usn (+) = t.xidusn 20:07:50 8 and rs.usn (+) = t.xidusn 20:07:50 9 and s.saddr = t.ses_addr 20:07:51 10 order by 7, 6, 1 20:07:52 11 /
no rows selected
Elapsed: 00:00:00.81
20:07:52 SYSTEM>select sid, serial#, username, taddr from
v$session where sid=11;
Sid SERIAL# Utilisateur TADDR ------- ---------- --------------- --------
11 142 TEST 1 row selected.
Elapsed: 00:00:00.71
20:07:53 SYSTEM>select * from v$transaction;
no rows selected
Elapsed: 00:00:00.41
20:07:55 SYSTEM>
Then during about a minute the process oracle.exe takes 75% of CPU and my disk works.
As you can see, I killed the TEST session at 20:07:45 after the second "update" (executed
at 20:06:11) and just after "alter system kill session", taddr in v$session is null and the row in
v$transaction no more exists.
(The first update+rollback was there only to verify the duration of a rollback of this statement.)
Thanks for your help
Michel
Received on Fri Dec 05 2003 - 09:23:50 CST
 |
 |