It 's a bit of time I do not write on my blog, but alas, this time working with Oracle has absconded and, consequently, also the ideas from which I draw my posts: (
This morning, however, a simple operation DROP a user in my db test initiated an Oracle error ORA-01940
: can not DROP a user is currently connected That
Abstract:
diagram above is the backend application web-based jsp made and run by Tomcat. Of course, before you run, user-system, drop the schema you want, I am concerned to warn users that they were working in the test of effetturare log out, then, given a reasonable period of time, I stopped the Tomcat server .
Time:
I thought that any hung sessions db level should depend on the presence of some users still connected with the ongoing operations in the test when I ran the stop the Tomcat server.
The view v $ session provides all the session information for active / inactive / kill and it is from this that one must start to do some diagnostics. The following SELECT statement queries the view just join in with another view of the system, ie
v $ process in order to get the id, the serial number and status of the session, as well as the PID of the process to it connected.
SQL> select s.sid, s.serial #, s.status, p.spid 
from v $ session s, v $ process p WHERE
s.username = 'MyUser' p.addr and (+) = s.paddr; from v $ session s, v $ process p WHERE 
The result you get is something like: SID SERIAL # SPID
STATUS ---------- ---------- ----- ------------ 12065 136 48808 135 31 595 INACTIVE INACTIVE
11092 12030 132 12 779 INACTIVE Now, inactive sessions can be terminated with the following command (to be repeated for each session to be terminated, and of course to run as a user with DBA privileges):
SQL> alter system kill session '';
rerun the query from v $ session view is that the sessions will be passed to the "KILLED", however, as has happened to me, it can happen that after a few moments, new sessions (or even when you kill them) come back again to the "INACTIVE" again making it impossible to run the drop of the user. I personally believe that Oracle tries to bring completed the job submitted by the Tomcat server db and encapsulated in the sessions described above, but having already lost the connection to the Tomcat, these sessions are always in the "INACTIVE".
The solution is really simple, just put it in the lock before you drop idle sessions:
SQL> alter user MyUser account lock;
You can then proceed to drop the user with peace of mind!
Conclusion:
starting from system views it solves everything! ;)(so you can copy and distribute it). I put it because one of the dwarfs have sunglasses -> need to see -> views of Oracle -> sclero:-D
The image of early post with the garden gnomes I've recovered from
here and is released under Creative Commons
Attribution-No Derivative Works 2.0 Generic
<#sid,#serial>
