oracle 无法删除当前已连接的用户
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4169441/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can't drop a user that is currently connected
提问by Yevgeniy Brikman
I'm trying to reinitialize an Oracle DB and am seeing the following error:
我正在尝试重新初始化 Oracle DB 并看到以下错误：
[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 ONEis connected: this is an instance on my local computer, no outside connections and I justrebooted 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?
问题是没有人连接：这是我本地计算机上的一个实例，没有外部连接，我刚刚重新启动并且没有运行其他任何东西。我唯一能想到的是 Oracle 可能有一些后台（清理？）任务正在运行，导致此问题，但我不知道如何查找/管理它。有任何想法吗？
Update: this script actually drops and reinitializes a bunch of tables, and after trying to re-run it a few times, I got the sameerror message but on a differenttable: Failed to execute: drop user csmy cascade. After a few more tries, it moved onto yet anotheruser: Failed to execute: drop user deb cascade. Something seems to be locking these tables, one at a time, in alphabetical order!
更新：这个脚本实际上下降并重新初始化了一堆表格，并试图重新进行了几次，我得到了同样的错误信息，但在不同的表：Failed to execute: drop user csmy cascade。经过几次尝试，它转移到另一个用户：Failed to execute: drop user deb cascade。似乎有什么东西按字母顺序锁定了这些表，一次一个！
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.
更新 2：在重新运行脚本大约 15 次之后——每次都在字母表中稍微靠前的桌子上失败——它已经完成了并且一切正常。我仍然很想知道到底发生了什么 - 我最好的猜测是一些后台 Oracle 进程，但我不知道如何检查。
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 cascadecommand 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.
更新 3：我上次重新运行脚本时再次遇到了同样的问题，这次在用户“cap”上失败了。为了尝试一些新的东西，我启动了 sqlplus 并手动运行了drop user cap cascade命令，瞧，它运行得很好。我尝试了脚本，它运行完成。因此，由于手动删除用户没有问题，我强烈怀疑脚本本身是罪魁祸首。
回答by Gary Myers
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.
不同模式中的对象之间可能存在依赖关系，这可能会阻止模式 A 中的对象被删除，直到模式 B 中的对象被删除。因此，如果最初可能无法删除架构 A，但如果架构 B 已被删除，则重试成功。
回答by JOTN
Have you queried from v$session to see who is connected? You might have an application running somewhere that re-connects automatically. You can always start the database in restricted mode or don't start the listener and run your script from a local connection.
你有没有从 v$session 查询过谁连接了？您可能在某处运行了一个应用程序，该应用程序会自动重新连接。您始终可以在受限模式下启动数据库，或者不启动侦听器并从本地连接运行脚本。
回答by ThinkJet
Check if all connections closed properly in case of exception.
Look at JDBC connection properties. If connection pooling or caching enabled some connections from previous run may still alive.
检查所有连接是否在异常情况下正确关闭。
查看 JDBC 连接属性。如果启用了连接池或缓存，则之前运行的某些连接可能仍处于活动状态。
回答by Sten Vesterli
Without seeing your script it's hard to tell exactly what happens. But I can assure you that the database itself never connects to specific users.
如果没有看到你的脚本，就很难确切地知道发生了什么。但我可以向您保证，数据库本身永远不会连接到特定用户。
What you can do is let your code log on (I assume your code runs as a DBA user) and loop through all users you want to drop. For each, issue REVOKE CREATE SESSION FROM That disables login so any mysterious code will be unable to connect.
您可以做的是让您的代码登录（我假设您的代码以 DBA 用户身份运行）并遍历您想要删除的所有用户。对于每个，发出 REVOKE CREATE SESSION FROM 禁用登录，因此任何神秘代码将无法连接。
回答by René Nyffenegger
Perhaps, if you had tried a
也许，如果你曾尝试过
ALTER SYSTEM DISCONNECT SESSION '<sid>,<serial#>' IMMEDIATE
(for each session of the user you try to drop) before dropping the user, it would have worked?
（对于您尝试删除的用户的每个会话）在删除用户之前，它会起作用吗？
Just guesswork.
只是猜测。
