Oracle Error 01940
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Dropping connected users in Oracle database up vote 20 down vote favorite 7 I want to drop some users in Oracle DB using sqlplus but I am getting error: SQL> DROP USER test CASCADE; DROP USER test CASCADE * ERROR at line 1: ORA-01940: cannot drop a user that is currently connected I followed the link in SO to find out the sessions - Dropping a connected user from an Oracle 10g database schema But when I ran the command I am not getting any results: SQL> select sid,serial# from v$session where username = 'test'; no rows selected Please help me how to drop users in this case. oracle share|improve this question edited Mar 29 '13 at 13:59 Yasir Arsanukaev 7,50222550 asked Mar 27 '13 at 17:03 Chaitanya 5,514114787 You answer may be here: anilanbu.blogspot.com/2012/02/… –Art Mar 27 '13 at 17:29 I tried that but I am not getting any rows when I tried to find out the sessions. –Chaitanya Mar 27 '13 at 17:34 1 I found out why I am not getting session records, my where condition should be on username='TEST' –Chaitanya Mar 27 '13 at 17:40 @user2065083-If you still get ORA-01940:... then check the query again. –Art Mar 27 '13 at 17:40 add a comment| 8 Answers 8 active oldest votes up vote 32 down vote accepted SELECT s.sid, s.serial#, s.status, p.spid FROM v$session s, v$process p WHERE s.username = 'TEST' --<<<-- AND p.addr(+) = s.paddr / Pass actual SID and SERIAL# values for user TEST then drop user...: ALTER SYSTEM KILL SESSION '
line 1: ORA-01940: cannot drop a user that is currently connected /// Before the kill session lock the account for further connection establish. SQL> SQL> alter user sprtsg4 account lock; User altered. SQL> SQL> SET LINESIZE 100 SQL> COLUMN spid FORMAT A10 SQL> COLUMN username FORMAT A10 SQL> COLUMN program FORMAT A45 SQL> SQL> SELECT s.inst_id, 2 s.sid, 3 s.serial#, 4 p.spid, s.username, 5 6 s.program 7 FROM gv$session s 8 JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id WHERE s.type != 'BACKGROUND'; 9 INST_ID SID SERIAL# SPID USERNAME PROGRAM ---------- ---------- ---------- ---------- ---------- --------------------------------------------- 1 49 7543 4360 SYS sqlplus@acs7 (TNS V1-V3) 1 100 4663 4153 SYS sqlplus@acs7 (TNS V1-V3) http://stackoverflow.com/questions/15664924/dropping-connected-users-in-oracle-database 1 141 15044 4626 SPRTSG4 JDBC Thin Client 1 16 33035 4628 SPRTSG4 JDBC Thin Client 1 71 16974 4630 SPRTSG4 JDBC Thin Client 1 83 58119 4632 SPRTSG4 JDBC Thin Client 1 90 61930 4634 SPRTSG4 JDBC Thin Client 1 http://anuragkumarjoy.blogspot.com/2015/05/solution-ora-01940-cannot-drop-user.html 121 28526 3065 SYSMAN OMS 1 31 44985 3076 SYSMAN OMS 1 122 31487 3078 SYSMAN OMS 1 140 11579 3366 DBSNMP emagent@acs7 (TNS V1-V3) INST_ID SID SERIAL# SPID USERNAME PROGRAM ---------- ---------- ---------- ---------- ---------- --------------------------------------------- 1 129 121 25590 DBSNMP emagent@acs7 (TNS V1-V3) 1 152 3737 4636 SPRTSG4 JDBC Thin Client 1 79 615 4615 SYSMAN OMS 1 119 22358 4638 SPRTSG4 JDBC Thin Client 1 6 6899 4640 SPRTSG4 JDBC Thin Client 1 46 7585 4644 SPRTSG4 JDBC Thin Client 1 151 38346 4648 SPRTSG4 JDBC Thin Client 18 rows selected. SQL> alter system kill session '&id,&serial'; Enter value for id: 151 Enter value for serial: 38346 old 1: alter system kill session '&id,&serial' new 1: alter system kill session '151,38346' System altered. SQL> / Enter value for id: 46 Enter value for serial: 7585 old 1: alter system kill session '&id,&serial' new 1: alter system kill session '46,7585' System altered. SQL> SQL> SQL> drop user sprtsg4 cascade; User dropped. SQL> SQL> SQL> SQL> SQL> SQL> Posted by ANURAG KUMAR PANDEY at 4:09:00 AM Email ThisBlogThis!S
Shipped with confidence Sponsored by Text only ORA-01940: cannot drop a user that is currently connected 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 https://snipt.net/belano/ora-01940-cannot-drop-a-user-that-is-currently-connected/ 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 http://appsdbaworkshop.com/?p=1226 42 43SQL> drop user strmadmin cascade; drop user strmadmin cascade ERROR at line 1: ORA-01940: cannot drop a user that is currently connected Solution of The Problem: Find out the connected user sid and serial# by, SQL> select sid, serial# from v$session where username = 'STRMADMIN'; SID SERIAL# — — 268 1268 315 1223 Before killing session you may wish to lock the account for further oracle error connection attempts. This is extremely necessary to drop a user who automatically establish session like to drop an application user or to drop a user who perform batch jobs. SQL> Alter user strmadmin account lock; Now kill the connected session. SQL> alter system kill session '268,1268'; System altered. SQL> alter system kill session '315,1223'; System altered. It is possible to force the kill by adding the IMMEDIATE keyword: SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; And then drop the user. SQL> drop oracle error 01940 user strmadmin cascade; User dropped. Alternative solution Connect all clients connected to Oracle with this user name. You may have a sqlplus client connect in another window with the user that you are trying to delete. You can also kill any existing session using the web admin tool that comes with Oracle 10g. * Goto admin interface: http://localhost:8888/apex/ as "admin" (default password for admin is adminadmin) * Goto Administration -> Monitor -> Sessions * Select a session by the user you are trying to drop * Press "Kill Session" SQL> drop user strmadmin cascade; drop user strmadmin cascade ERROR at line 1: ORA-01940: cannot drop a user that is currently connected Solution of The Problem: Find out the connected user sid and serial# by, SQL> select sid, serial# from v$session where username = 'STRMADMIN'; SID SERIAL# — — 268 1268 315 1223 Before killing session you may wish to lock the account for further connection attempts. This is extremely necessary to drop a user who automatically establish session like to drop an application user or to drop a user who perform batch jobs. SQL> Alter user strmadmin account lock; Now kill the connected session. SQL> alter system kill session '268,1268'; System altered. SQL> alter system kill session '315,1223'; System altered. It is possible to force the kill by adding the IMMEDIATE keyword: SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; And then drop the user. SQL> drop user strmadmin cascade; User dropped. Alternative solution Connect all clients
Oracle VM Vmware/VirtualBox Monitoring OEM OPS Home » Database » ORA-01940: cannot drop a user that is currently connected - Oracle 12c ORA-01940: cannot drop a user that is currently connected - Oracle 12c Recently when i was trying to drop one of the schema then the drop command was failing with below error: SQL> drop user FINTEST5 cascade; drop user FINTEST5 cascade * ERROR at line 1: ORA-01940: cannot drop a user that is currently connected The Error message itself is very clear the the user is connected so it cannot be dropped. Now we must find out the session for this specific users and kill those sessions in order to drop this user. >> Find out the session for the connected user SQL> SELECT sid, serial#, username, logon_time, (last_call_et / 60 / 60 / 24 ) "Days Inactive" FROM v$session WHERE username LIKE 'FINTEST5'; SID SERIAL# USERNAME LOGON_TIME Days Inactive ---------- ---------- ------------------------------ ---------- ------------- 3 28047 FINTEST5 13-JAN-16 .001597222 >> Kill all listed sessions SQL> ALTER SYSTEM KILL SESSION '3,28047'; System altered. SQL> >> Check any active session again before dropping the user SQL> SELECT sid, serial#, username, logon_time, (last_call_et / 60 / 60 / 24 ) "Days Inactive" FROM v$session WHERE username LIKE 'FINTEST5'; no rows selected >> Now we are read to drop the user SQL> drop user FINTEST5 cascade; User dropped. SQL> This can be done using TOAD or Oracle enterprise manager as well. We need to check all connected users for the user which we want to drop and then kill all sessions belonging to that users. thanks for reading. regards, X A H E E R March 4, 2016 admin Database, Oracle No Comment drop user, kill session, oracle12c, toadworld «zone is unavailable; attach suggested - Solaris 11 Non Global zone UDE-31623: operation generated ORACLE error 31623 - oracle 12c» Leave a Reply Cance