Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can't drop a user that is currently connected

I'm trying to reinitialize an Oracle DB and am seeing the following error:

  [sql] Failed to execute:  drop user conns cascade
  [sql] java.sql.SQLException: ORA-01940: cannot drop a user that is currently connected
  [sql] Failed to execute:  create user conns identified by conns default tablespace tbs_conns temporary tablespace temp1
  [sql] java.sql.SQLException: ORA-01920: user name 'CONNS' conflicts with another user or role name

The problem is that NO ONE is connected: this is an instance on my local computer, no outside connections and I just rebooted and have not run anything else. The only thing I can think of is that Oracle might have some background (cleanup?) task running that is causing this issue, but I have no clue how to find/manage that. Any ideas?

Update: this script actually drops and reinitializes a bunch of tables, and after trying to re-run it a few times, I got the same error message but on a different table: Failed to execute: drop user csmy cascade. After a few more tries, it moved onto yet another user: Failed to execute: drop user deb cascade. Something seems to be locking these tables, one at a time, in alphabetical order!

Update 2: after re-running the script about 15 times - each time failing at a table slightly further along in the alphabet - it has gotten all the way through and things are working. I'd still love to know exactly what happened - my best guess is some background Oracle process, but I have no clue how to check.

Update 3: I ran into this same issue again the last time I re-ran the script, this time failing on user "cap". To try something new, I fired up sqlplus and manually ran the drop user cap cascade command and, lo and behold, it worked just fine. I tried the script and it ran to completion. Therefore, since manually dropping a user works w/o issues, I strongly suspect the script itself is to blame.

like image 570
Yevgeniy Brikman Avatar asked Nov 05 '22 06:11

Yevgeniy Brikman


1 Answers

Does the script use the same connection ? Is it trying top simultaneous drop multiple users ?

It looks like if it encounters an error, it simply continues to the next step.

There can be dependencies between objects in different schemas which may prevent an object in schema A being dropped until an object in schema B is dropped. As a result if may fail to drop schema A initially, but succeed on a retry if schema B has been dropped.

like image 69
Gary Myers Avatar answered Nov 13 '22 02:11

Gary Myers