Re: Disconnect connected sessions

From: makbo <makbo_at_pacbell.net>
Date: Thu, 24 Apr 2003 01:15:19 GMT
Message-ID: <3EA73AA6.7030807@pacbell.net>

Daniel Morgan wrote:
> "Yuri Weinstein (HotMail)" wrote:

>>Can you give me a query example?
>>
>>The reason I am dropping and recreating the db - testing, I need to test
>>some stuff.
>>
>>thx
>>
>><snipped>

>
>
> You still haven't explained ... "Why?"
>
> The entire concept of what you are trying to do sounds to me like either a
> great academic waste of time or a wonderful way to get your co-workers to want
> to toss you into a fountain ... in the middle of a snow storm.
>
> What's wrong with checking v_$session, seeing if anyone is connected, and then
> phoning them? Too courteous?
>
> If you do wish to proceed with this you'd best be prepared for the fact that
> after you will someone's session ... you may still not be able to drop the user
> for quite a long time while Oracle undoes currently running transactions: A
> phone call is much faster.
>
> Daniel Morgan
>

Yes, below is a query example (to answer the question of the OP). This can be run from a Unix shell script (or a Windows batch file with some naming conventions changed). I have never had a problem with waiting for the session to be killed.

To answer some other questions:

1. Why drop a user in a test database? Because it is easier and simpler than trying to explicitly drop every single object the user owns, plus revoking any roles, etc. That is why the "CASCADE" option exists, I think.

Let's say you want to test an import you are about to send to a customer; it's much more reproducible to start with a freshly-created user in a known state.

2) Why not call each connected user of the schema to be dropped before forcibly disconnecting their sessions? Because it might be 2:30 in the morning and you have a batch job which needs to do an import without human intervention. The users might be programmers who have forgotten to log out during the scheduled down time, or they might not even be human at all, such as JDBC connection pool sessions.

Mark Bole

query example:
set head off
set echo off
set termout off
set verify off
set pagesize 0
set feedback off
set timing off

spool /tmp/kill_sess.sql

select 'alter system disconnect session '''

       || s.sid || ',' || s.serial# || ''' immediate;' from v$session s
where s.username <in your list of users> and status <> 'KILLED';
spool off
/

set echo on
set verify on
set feedback on
set termout on

@/tmp/kill_sess

drop user <your schema owner> cascade; Received on Wed Apr 23 2003 - 20:15:19 CDT