I have Oracle 9.2
I view my running session with the query:
SELECT S92700.SYSUSR_VW."NAME", SID,SERIAL#, AUDSID
FROM v$session, S92700.SYSUSR_VW, S92700.SECUSR
WHERE S92700.SYSUSR_VW.SESSIONID = AUDSID AND S92700.SECUSR."NAME"=S92700.SYSUSR_VW."NAME"
Result:
NAME SID SERIAL# AUDSID
prot6 9 6503 78239
And i run the follow query to kill the session
alter system kill session '9,6503' immediate
SQL Command execution
Executed successfully in 0 s, 0 rows affected. Line 1, column 1
Execution finished after 0 s, 0 error(s) occurred.
The session still running
The same query in other schema is working
Best Answer
From a thread on AskTom, when a session is killed:
When you do DML on a table, Oracle may make the changes to the base table almost immediately, even if you don't commit. The other sessions don't see these changes yet thanks to multiversion read consistency but the actual physical block may already be overwritten with your uncommited work.
This is done because Oracle is optimized for commit. Most sessions should rarely rollback so Oracle anticipates by writing preemptively the changes. This is also one of the reasons that allows Oracle to have arbitrarily large transactions (limited only by the size of the undo tablespace). The changes are not delayed (much). This is also why you almost never wait for a commit in Oracle, even for multimillion-row DML.
One of the consequences is that rollback needs to undo the changes. The uncommited transaction is read back from the undo tablespace and each and every change is undone in reverse order. This can take longer than the initial DML for several reasons:
This explains why rollback can take a lot of time. You have few means to accelerate the process. In any case you should not:
If you shutdown the database normally (or transactional or immediate), Oracle will wait for the process to complete so that the database is left in a consistent state. If you shutdown
ABORT
, the database will be left in an inconsistent state (uncommited data written to disk) and Oracle will resume the rollback as soon as the database is restarted.The rollback is handled by smon, I wouldn't try to kill it.
Some session, after being killed, will remain in the
v$session
view, even after all their changes have been entirely rolled back. This is often because Oracle is waiting for the client to return a relevant error message, such as:If the client itself has already left or the network communication has been severed, the killed session may stay indefinitely until you restart the instance. I don't think these sessions take resources.