Dropping a connected user from an Oracle 10g database schema

up vote 6 down vote favorite share [fb] share [tw]

Is there a better way to forcefully disconnect all users from an Oracle 10g database schema than restarting the Oracle database services? 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? oracle link|improve this question asked Sep 17 '08 at 17:49 Joshua Starner 99116 100% accept rate

feedback

4 Answers

active oldest votes

up vote 11 down vote accepted

To find the sessions, as a DBA use 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 Then kill them with alter system kill session '<sid>,<serial#>' (e.g. 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>' 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'; link|improve this answer