ORA-01940: Cannot drop a user that is currently connected
It’s been a while since I wrote on my blog, but, alas, in this period the work with Oracle has disappeared and, consequently, also the ideas from which I draw my posts 🙁
This morning, however, a simple operation of DROP of a user of my test db originated the error Oracle ORA-01940: cannot DROP a user that is currently connected
Premise: the above scheme constitutes the backend of a web-based application created in jsp and managed from Tomcat. Obviously, before running, as a user system, the drop of the desired schema, I took care to warn the users who were working in the test environment to log out, therefore, after a reasonable amount of time, I stopped the Tomcat server.
Procedure: I thought that any sessions hanging at the database level should depend on the presence of some user still connected and with operations in progress in the test environment when I stopped the Tomcat server.
The v$session view provides all the information on active/inactive/killed sessions and it is precisely from this that you have to start to do some diagnostics.
The following SELECT statement queries this view in joining with another system view, v$process in order to obtain the id of the serial code and the session status, as well as the PID of the process connected to it.
SQL> select s.sid, s.serial #, s.status, p.spid
from v $ session s, v $ process p
where s.username = 'MYUSER'
and p.addr (+) = s.paddr;
The result is something like this:
SID SERIAL # STATUS SPID
———- ———- —————–
176 48808 INACTIVE 15365
185 31595 INACTIVE 15630
182 12779 INACTIVE 16992
Now, inactive sessions can be terminated with the following command (to be repeated for each session to be terminated, obviously and to be launched as a user with DBA privileges):
SQL> alter system kill session '<#sid, #serial>';
Rerunning the query from the v $ session view you will get that the sessions have passed to the “KILLED” state, however, as it happened to me, it can happen that after a few moments new sessions (or even the same ones you just killed) go back to the state ” INACTIVE “making it impossible to drop the user again.
Personally I believe that Oracle tries to complete the jobs submitted by Tomcat on the database server and encapsulated in the above sessions, but having lost the connection with Tomcat, these sessions always remain in the “INACTIVE” state.
The solution is actually simple, in fact, it is enough to lock the user before dropping the inactive sessions:
SQL> alter user MYUSER account lock;
So you can proceed with the user drop in peace!
When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert. This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.