Dropping a connected user from an Oracle 10g database schema

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';

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)