Killing the session will not clear the locks
The session on the remote database will remain idle waiting for input until the network read times out
Only then the kill session is processed
and locks are released
Solution Description
Options:
========
You can shutdown and restart the database or use the ORAKILL utility to kill threads
Oracle has provided an ORAKILL utility that will kill shadow threads
Each user
s connection is represented by a thread in the Oracle process
If a user
s session is killed
then their Oracle session is killed
not the thread
Oracle has provided an ORAKILL utility which can be passed a thread ID and will kill the specified thread
To make sure you do not kill a background process (which would crash your database)
you must perform a select to ensure you get the correct thread
select p
spid
OS Thread
b
name
Name
User
s
osuser
s
program
from v$process p
v$session s
v$bgprocess b
where p
addr = s
paddr
and p
addr = b
paddr UNION ALL
select p
spid
OS Thread
s
username
Name
User
s
osuser
s
program
from v$process p
v$session s
where p
addr = s
paddr
and s
username is not null;
This will list all Shadow processes and backgound processes
Each shadow process will show the thread ID
this is what must be killed via the ORAKILL utility
The kill session behavior is mentioned in the ORACLE
Server Administrator
s Guide (pg
)
It does not explicitly indicate what happens to the session while it is in the KILLED PSEUDO state
What
s happening is that PMON periodically checks to see if any sessions have been killed
If it finds one
it attempts to rollback the transaction for that session(that was in progress when it was killed)
The reason this can take a long time is because PMON may have more than one transaction to rollback at a time(if other sessions have been killed
or if processes have died etc)
Thus
it may take a while to finally cleanup the killed session and have it disappear from the session monitor
The system i/o monitor correctly shows the reads and writes being performed by PMON in order to rollback the session
s transaction
PMON will not delete the session object itself until the client connected to that session notices that it has been killed
Therefore
the sequence of events is:
) alter system kill session is issued
the STATUS of the session object in V$SESSION becomes KILLED
its server becomes PSEUDO
) PMON cleans up the *resources* allocated to the session(i
e
rolls back its transaction
releases its locks
etc)
) the entry in V$SESSION remains there until the client of that session (the client is the process associated with the OSUSER
MACHINE
PROCESS columns in the V$SESSION view) tries to do another request
) the client attempts another SQL statement and gets back ORA
) PMON can now remove the entry from V$SESSION
This behavior is necessary because the client still has pointers to the session object even though the session has been killed
Therefore
the object cannot be deleted until the client is no longer pointing at it
From:http://tw.wingwit.com/Article/program/Oracle/201311/17197.html
