Viewed 100K+ times! This question is You Asked Hi TomWhen we do alter system kill 'sid, serial#'. Are we killing the user process or the server process? Because sometimes i see that after the session is killed the query is still running in the sessions even shows KILLED sometimes takes days to disappear! Sometimes we got some problems with large batch jobs in testing enviroment, we killed from sql plus but the job keeps on going!What happens if we kill the server process with kill -9 instead of issueing alter system kill?cheerscheers and Tom said...see =100:11:::::P11_QUESTION_ID:1058832409881 the job isn't "going on" it is actually "going back" -- it is rolling back.If the job took an hour to get where it is, it might take even LONGER to "undo" what its done.You can monitor how the killed session is doing as far as rolling back is concerned in v$transaction via the used_ublk column.Consider this example. In a schema "big_table" i started a delete against a 1,000,000 row indexed table. Partway through -- I killed it. This is what you'll see (you need to be more selective on the query against v$transaction of course, I just had one active DML session going)ops$tkyte@ORA817DEV.US.ORACLE.COM> select used_ublk from v$transaction; USED_UBLK---------- 57501 row selected.it generated that much undo so far...ops$tkyte@ORA817DEV.US.ORACLE.COM> @showsqlUSERNAME SID_SERIAL STATUS MODULE ACTION --------------- --------------- ---------- --------------- --------------- OPS$TKYTE '7,665' ACTIVE 01@ showsql.sql BIG_TABLE '8,1082' ACTIVE SQL*Plus 2 rows selected.BIG_TABLE(8,1082) ospid = 13903 command = 7 program = sqlplus@aria-dev (TNS V1-V3) dedicated server=13904Monday 10:24 Monday 10:38 last et = 822delete from big_tableI kill it and get the message "it is marked for kill". That means the server heard us and is in the process of rolling back but it'll take a whileops$tkyte@ORA817DEV.US.ORACLE.COM> alter system kill session '8,1082';alter system kill session '8,1082'*ERROR at line 1:ORA-00031: session marked for killops$tkyte@ORA817DEV.US.ORACLE.COM> select username, status from v$session where username = 'BIG_TABLE';USERNAME STATUS-------------------- ----------BIG_TABLE KILLED1 row selected.ops$tkyte@ORA817DEV.US.ORACLE.COM> select used_ublk from v$transaction; USED_UBLK---------- 53271 row selected.ops$tkyte@ORA817DEV.US.ORACLE.COM> / USED_UBLK---------- 52021 row selected.we can watch the used_ublk shrink and even use that to estimate when it'll be doneops$tkyte@ORA817DEV.US.ORACLE.COM> / USED_UBLK---------- 44701 row selected.ops$tkyte@ORA817DEV.US.ORACLE.COM> / USED_UBLK---------- 29141 row selected.ops$tkyte@ORA817DEV.US.ORACLE.COM> select username, status from v$session where username = 'BIG_TABLE';USERNAME STATUS-------------------- ----------BIG_TABLE KILLED1 row selected.session is still there...ops$tkyte@ORA817DEV.US.ORACLE.COM> select used_ublk from v$transaction; USED_UBLK---------- 22621 row selected.ops$tkyte@ORA817DEV.US.ORACLE.COM> / USED_UBLK---------- 14301 row selected.ops$tkyte@ORA817DEV.US.ORACLE.COM> / USED_UBLK---------- 11031 row selected.ops$tkyte@ORA817DEV.US.ORACLE.COM> select username, status from v$session where username = 'BIG_TABLE';USERNAME STATUS-------------------- ----------BIG_TABLE KILLED1 row selected.ops$tkyte@ORA817DEV.US.ORACLE.COM> select used_ublk from v$transaction; USED_UBLK---------- 10481 row selected.ops$tkyte@ORA817DEV.US.ORACLE.COM> / USED_UBLK---------- 4891 row selected.ops$tkyte@ORA817DEV.US.ORACLE.COM> /no rows selectednow we are done rolling back and...ops$tkyte@ORA817DEV.US.ORACLE.COM> select username, status from v$session where username = 'BIG_TABLE';no rows selectedops$tkyte@ORA817DEV.US.ORACLE.COM> the session is goneIf you kill -9, it might appear that the session is gone "faster" but not really. We still have to roll back (obviously). We might be doing on demand recovery of the blocks which makes it "appear" faster but we still have to recover the transaction. kill -9 is not something I recommend, especially if you use the above technique to see it rolling back. Rating (57 ratings)Is this answer out of date? If it is, please let us know via a Comment Comments Comment server processA reader, August 12, 2002 - 11:22 am UTC
Sqlgate 2010 For Sql Server Serial
DOWNLOAD: https://tlniurl.com/2vGjTI
2ff7e9595c
Comments