从 Oracle 10g 数据库模式中删除连接的用户
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/85804/
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
Dropping a connected user from an Oracle 10g database schema
提问by Joshua Starner
Is there a better way to forcefully disconnect all users from an Oracle 10g database schema than restarting the Oracle database services?
有没有比重新启动 Oracle 数据库服务更好的方法来强制断开所有用户与 Oracle 10g 数据库架构的连接？
We have several developers using SQL Developer connecting to the same schema on a single Oracle 10g server. The problem is that when we want to drop the schema to rebuild it, inevitably someone is still connected and we cannot drop the database schema or the user while someone is still connected.
我们有几个开发人员使用 SQL Developer 连接到单个 Oracle 10g 服务器上的相同模式。问题是，当我们想要删除模式以重建它时，不可避免地有人仍然连接着，而我们不能在有人仍然连接时删除数据库模式或用户。
By the same token, we do not want to drop all connections to other schemas because other people may still be connected and testing with those schemas.
出于同样的原因，我们不希望删除与其他模式的所有连接，因为其他人可能仍然连接并使用这些模式进行测试。
Anyone know of a quick way to resolve this?
有人知道解决这个问题的快速方法吗？
回答by Sten Vesterli
To find the sessions, as a DBA use
要查找会话，作为 DBA 使用
select sid,serial# from v$session where username = '<your_schema>'
select sid,serial# from v$session where username = '<your_schema>'
If you want to be sure only to get the sessions that use SQL Developer, you can add and program = 'SQL Developer'. If you only want to kill sessions belonging to a specific developer, you can add a restriction on os_user
如果您只想确保获取使用 SQL Developer 的会话，您可以添加and program = 'SQL Developer'. 如果您只想终止属于特定开发人员的会话，则可以添加限制os_user
Then kill them with
然后用
alter system kill session '<sid>,<serial#>'(e.g.
alter system kill session '39,1232')
alter system kill session '<sid>,<serial#>'（例如
alter system kill session '39,1232'）
A query that produces ready-built kill-statements could be
生成现成的终止语句的查询可能是
select 'alter system kill session ''' || sid || ',' || serial# || ''';' from v$session where username = '<your_schema>'
select 'alter system kill session ''' || sid || ',' || serial# || ''';' from v$session where username = '<your_schema>'
This will return one kill statement per session for that user - something like:
这将为该用户的每个会话返回一个 kill 语句 - 类似于：
alter system kill session '375,64855';
alter system kill session '375,64855';
alter system kill session '346,53146';
alter system kill session '346,53146';
回答by Chand Priyankara
Find existing sessions to DB using this query:
使用此查询查找到数据库的现有会话：
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';
you'll see something like below.
你会看到类似下面的内容。
Then, run below query with values extracted from above results.
然后，使用从上面的结果中提取的值运行下面的查询。
ALTER SYSTEM KILL SESSION '<put above s.sid here>,<put above s.serial# here>';
Ex: ALTER SYSTEM KILL SESSION '93,943';
例如： ALTER SYSTEM KILL SESSION '93,943';
回答by RLapinski
my proposal is this simple anonymous block:
我的建议是这个简单的匿名块：
DECLARE
lc_username VARCHAR2 (32) := 'user-name-to-kill-here';
BEGIN
FOR ln_cur IN (SELECT sid, serial# FROM v$session WHERE username = lc_username)
LOOP
EXECUTE IMMEDIATE ('ALTER SYSTEM KILL SESSION ''' || ln_cur.sid || ',' || ln_cur.serial# || ''' IMMEDIATE');
END LOOP;
END;
/
回答by RLapinski
Make sure that you alter the system and enable restricted session before you kill them or they will quickly log back into the database before you get your work completed.
确保在杀死它们之前更改系统并启用受限会话，否则它们会在您完成工作之前快速登录回数据库。
回答by LinuxQuestions.org
just use SQL :
只需使用 SQL ：
disconnect;
conn tiger/scott as sysdba;
回答by Dave Costa
Have you tried ALTER SYSTEM KILL SESSION? Get the SID and SERIAL# from V$SESSION for each session in the given schema, then do
您是否尝试过 ALTER SYSTEM KILL SESSION？从 V$SESSION 获取给定模式中每个会话的 SID 和 SERIAL#，然后执行
ALTER SCHEMA KILL SESSION sid,serial#;
ALTER SCHEMA KILL SESSION sid, serial#;
回答by Mac
Just my two cents : the best way (but probably not the quickest in the short term) would probably be for each developer to work on his own database instance (see rule #1 for database work).
只需我的两分钱：最好的方法（但短期内可能不是最快的）可能是每个开发人员都在自己的数据库实例上工作（请参阅规则 #1 的数据库工作）。
Installing Oracle on a developer station has become a no brainer since Oracle Database 10g Express Edition.
自Oracle 数据库 10g 快捷版以来，在开发人员站上安装 Oracle 变得轻而易举。
