Re: Disconnect connected sessions

From: Yuri Weinstein \(HotMail\) <yuriw_at_hotmail.com>
Date: Fri, 25 Apr 2003 20:36:42 GMT
Message-ID: <u%gqa.1276$kk5.109418635@newssvr21.news.prodigy.com>

Thank you all for the help.
Here is the final script is you are interested. It will kill sessions before dropping and creating a new ora user.

---

#! /bin/sh

# Parameters
# $1 = username
# $2 = password
# $3 = tablespace
# $4 = sid
# $5 = dba username
# $6 = dba password

if [ $# -ne 6 ]; then
  echo 1>&2 Usage: "$0 <username> <password> <tablespace> <sid> <dba username> <dba password>"
  exit 1
fi

# XXX: Turned off grant of DBA privileges
# GRANT CONNECT, RESOURCE, DBA TO $1;
sqlplus $5/$6@$4 <<EOF

DECLARE
  l_cursor INTEGER;
  l_exec_result INTEGER;

--
BEGIN
  FOR l_session_rec IN (SELECT sid, serial# FROM v\$session WHERE username
='$1')
  LOOP
     l_cursor := DBMS_SQL.Open_Cursor;
     DBMS_SQL.Parse (l_cursor, 'ALTER SYSTEM KILL SESSION ''' ||
l_session_rec.sid || ',' || l_session_rec.serial# || '''', DBMS_SQL.native);
     l_exec_result := DBMS_SQL.Execute (l_cursor);
     DBMS_SQL.Close_Cursor (l_cursor);
  END LOOP;
END;
/

DROP USER $1 CASCADE;
CREATE USER $1 IDENTIFIED BY $2
  DEFAULT TABLESPACE $3 TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT QUOTA UNLIMITED ON $3
  QUOTA UNLIMITED ON TEMP ACCOUNT UNLOCK;
GRANT CONNECT, RESOURCE TO $1;
EOF

exit 0
===========================


"makbo" <makbo_at_pacbell.net> wrote in message
news:3EA73AA6.7030807_at_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 Fri Apr 25 2003 - 15:36:42 CDT