So you want to update the schema, which requires to drop all users and you see that your script is complaining that some of the users (in oracle users are the schemas) are still conneted and cannot be dropped. This would normally mean some one is running an application/service/App server, which is holding some connections to your database.
You have been nice to all, mailed the group, pinged some of your fellow developers, but there is a rogue application holding on to a user/schema. What would you do. wait for ever or take matter into your hands
Here is some Mojo and fire power for you to boot/kill those users force fully ( ofcourse you need to have SysDba access to the database, otherwise you are out of luck )
- find the sessions for the users/schema , as a DBA use :
select sid,serial# from v$session where username = '<user_schema>'
- Then kill them with :
alter system kill session '<sid>,<serial#>'
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';
- Now Drop the user : drop user <
That’s it – The User is vaporized !