在 Oracle 中删除用户级联
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4096560/
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
drop user cascade in Oracle
提问by UserControl
I need to be able to drop a specific user (which may have active sessions) from the batch without any user interaction. I don't care about active sessions and want them to be dropped and rolled back. For Microsoft SQL i would do similar task with a single line:
我需要能够在没有任何用户交互的情况下从批处理中删除特定用户（可能具有活动会话）。我不关心活动会话并希望它们被删除和回滚。对于 Microsoft SQL，我会用一行来完成类似的任务：
osql -E -S localhost -b -Q "use master if ((select name from sysdatabases where name='%DB%') is not null) begin alter database [%DB%] set single_user with rollback immediate drop database [%DB%] end"
How do i do it for Oracle (10g XE on Windows)?
我如何为 Oracle（Windows 上的 10g XE）执行此操作？
My current batch is:
我目前的批次是：
sqlplus sys/*** as SYSDBA @delete1.sql >delete.log
sqlplus sys/***@XE as SYSDBA @delete2.sql >>delete.log
where delete1.sql:
其中delete1.sql：
startup force;
exit;
and delete2.sql:
并删除2.sql：
drop user MYUSER cascade;
exit;
This is ugly as hell and takes too long comparing to the split second of MSSQL solution.
与 MSSQL 解决方案的瞬间相比，这太丑了，而且花费的时间太长。
采纳答案by René Nyffenegger
It should work if you use the following script (here named drop_user_with_active_sessions.sql):
如果您使用以下脚本（此处命名为drop_user_with_active_sessions.sql），它应该可以工作：
set verify off
begin
for s in (
select
sid, serial#
from
v$session
where
username = '&1'
) loop
execute immediate
'alter system kill session ''' ||
s.sid || ',' ||
s.serial# || ''' immediate';
end loop;
execute immediate 'drop user &1';
end;
/
exit
And the use it with
并使用它
sqlplus username/password@instance @c:\path\to\drop_user_with_active_session.sql MYUSER
回答by gsiems
In addition to "alter system kill session" mentioned above I've also needed to preface the kill session with something like:
除了上面提到的“更改系统终止会话”之外，我还需要在终止会话之前使用以下内容：
execute immediate 'ALTER SYSTEM DISCONNECT SESSION ''' ||
to_char(s.sid) || ', ' || to_char(s.serial#) || ''' IMMEDIATE'
回答by Stephanie Page
It's a very, very bad idea to take a construct from one Database platform and assume I can run the exact same thing on a different platform. For example. Oracle has Create OR REPLACE procedure. MSSS isn't quite so simple. MSSS you can make a "temp" table with #name, in Oracle we use DDL. While dropping a user to recreate a fresh environment may have been the simplest approach on MSSS, perhaps there's a more Oracle-centric way to accomplish the same thing. It's a very good idea to ask for help on how to accomplish a task instead of why your way isn't working.
从一个数据库平台获取构造并假设我可以在不同的平台上运行完全相同的东西，这是一个非常非常糟糕的主意。例如。Oracle 有 Create OR REPLACE 过程。MSSS 并不是那么简单。MSSS 你可以用#name 创建一个“临时”表，在 Oracle 中我们使用 DDL。虽然删除用户以重新创建新环境可能是 MSSS 上最简单的方法，但也许有一种更以 Oracle 为中心的方法来完成同样的事情。寻求有关如何完成任务的帮助而不是为什么您的方法行不通是一个很好的主意。
First, does the app being tested do DDL? to the tables and other objects?
首先，正在测试的应用程序是否执行 DDL？到表和其他对象？
If it only changes data, they way Oracle prefers apps to work, then why do you have to recreate all the objects. You just need to get the data back to the starting point.
如果它只更改数据，他们的方式 Oracle 更喜欢应用程序工作，那么您为什么必须重新创建所有对象。你只需要让数据回到起点。
Have you looked into Flashback Database? You should be able to create a restore point... do whatever you want and then flashback the database to that point in time.
你有没有研究过闪回数据库？您应该能够创建一个还原点...做任何您想做的事情，然后将数据库闪回到那个时间点。
回答by Harrison
you can do Oracle SQL via the command promptand then do your cascade drop user.
您可以通过命令提示符执行Oracle SQL，然后执行级联删除用户。
I would recommend creating a sql script and executing it from the command line.
我建议创建一个 sql 脚本并从命令行执行它。
then you can wrap up command line text in your cmd/batch file.
然后你可以在你的 cmd/batch 文件中包含命令行文本。
but if you would like Oracle to handle the entire process I would recommend looking into the job/schedule environment
但如果您希望 Oracle 处理整个过程，我建议您查看作业/计划环境
