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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With