Shulou(Shulou.com)05/31 Report--

This article mainly shows you "ORA-01940 appears in oracle: how to delete the currently connected user", the content is simple and easy to understand, organized clearly, I hope to help you solve doubts, let Xiaobian lead you to study and learn "ORA-01940 appears in oracle: how to delete the currently connected user" this article bar.

During field operation, users need to be deleted first; when drop user is executed,"ORA-01940: cannot drop a user that is currently connected" is prompted, and the environment at that time is as follows:

Operating system: Windows XP

Database: Oracle 10.2.0.1

At that time, the time was relatively urgent, so I directly took the method of pulling the net cable from the bottom. This method was very useful. If drop user was executed again, it would be successful. Considering that you may not be allowed to take this simple and rude approach in all future occasions, I reproduced this error on the virtual machine and sought a more gentle solution.

First I opened two virtual machines, two instances on VM 1, opening three windows connected to the scott user, and one instance on VM 2, opening a window connected to the scott user. At this time, I logged in to sys user to perform drop user operation, and reproduced the above error.

Click here to fold or open

[oracle@enmoedu1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.. 3.0 Production on Fri Apr 17 21:25:40 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.. 3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop user scott cascade;

drop user scott cascade

*

ERROR at line 1:

ORA-01940: cannot drop a user that is currently connected

SQL>

First, we look at the connection status of scott users;

Click here to fold or open

SQL> select username,sid,serial# from v$session where username='SCOTT';

USERNAME SID SERIAL#

------------------------------ ---------- ----------

SCOTT 19 355

SCOTT 20 13693

SCOTT 26 15

SCOTT 127 7

SQL>

Next, we kill the above connection;

Click here to fold or open

SQL> alter system kill session'19,355';

System altered.

SQL> alter system kill session'20,13693';

System altered.

SQL> alter system kill session'26,15';

System altered.

SQL> alter system kill session'127,7';

System altered.

SQL>

Query the connection status of scott users again and confirm that all connections have been cleared;

Click here to fold or open

SQL>

SQL> select username,sid,serial# from v$session where username='SCOTT';

USERNAME SID SERIAL#

------------------------------ ---------- ----------

SCOTT 19 355

SCOTT 20 13693

SCOTT 26 15

SCOTT 127 7

SQL>

Don't panic and assume that these connections are still there; we just need to check the status of these connections.

Click here to fold or open

SQL>

SQL> select saddr,sid,serial#,paddr,username,status from v$session where username is not null;

SADDR SID SERIAL# PADDR USERNAME STATUS

-------- ---------- ---------- -------- ------------------------------ --------

578CC410 19 355 583AD258 SCOTT KILLED

578C9890 20 13693 583AD258 SCOTT KILLED

578B9390 26 15 583AD258 SCOTT KILLED

579DED90 120 95 5836FFB4 SYS ACTIVE

579CBD10 127 7 583AD258 SCOTT KILLED

SQL>

From the above query results, we know that all connections of scott user have been killed; now we execute drop user to see if it can succeed;

Click here to fold or open

SQL>

SQL> drop user scott cascade;

User dropped.

SQL>

As a result, ORA-01940 error was successfully resolved.

ORA-01940 appears in oracle: What should I do if I can't delete the currently connected user? Thank you for reading all the contents of this article! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.