从Oracle 10g数据库架构中删除连接的用户 [英] Dropping a connected user from an Oracle 10g database schema

问题描述

是否有比重新启动Oracle数据库服务更好的方法来强制所有用户与Oracle 10g数据库架构断开连接?

Is there a better way to forcefully disconnect all users from an Oracle 10g database schema than restarting the Oracle database services?

我们有几个使用SQL Developer的开发人员连接到单个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.

出于同样的原因，我们也不想删除与其他模式的所有连接，因为其他人可能仍然可以连接并使用这些模式进行测试.

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?

推荐答案

要查找会话，以DBA身份使用

To find the sessions, as a DBA use

select sid,serial# from v$session where username = '<your_schema>'

如果仅要确保获取使用SQL Developer的会话，则可以添加and program = 'SQL Developer'.如果您只想终止属于特定开发人员的会话，则可以在os_user

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

然后用

alter system kill session '<sid>,<serial#>'

(例如alter system kill session '39,1232')

产生现成的kill-statement的查询可能是

A query that produces ready-built kill-statements could be

select 'alter system kill session ''' || sid || ',' || serial# || ''';' from v$session where username = '<your_schema>'

这将为该用户在每个会话中返回一条kill语句-类似于:

This will return one kill statement per session for that user - something like:

alter system kill session '375,64855';

alter system kill session '346,53146';

这篇关于从Oracle 10g数据库架构中删除连接的用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！