Re: Disconnect connected sessions

I am not sure I understand you.

Can you give me a query example?

The reason I am dropping and recreating the db - testing, I need to test some stuff.

thx

"Anton Buijs" <remove_aammbuijs_at_xs4all.nl> wrote in message news:3e9f0fe7$0$49114$e4fe514c_at_news.xs4all.nl...
>
> Yuri Weinstein (HotMail) <yuriw_at_hotmail.com> schreef in berichtnieuws
> BXDna.139$Qj7.129_at_newssvr16.news.prodigy.com...
> | Hi all,
> |
> | I am using the script below to drop and re-create a user. However if
> | somebody is connect this script would not work.
> |
> | How would I disconnect all active sessions and make this script work?
> |
> | Thx
> |
> | YuriW
> |
> | ====================
> | #! /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
> | 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
> | ===============
> |
>
> Use an anonymous PL/SQL block where you query V$SESSION where
username=....
> and do execute immediate with statement "alter system kill session
> 'sid,serial';" with the sid and serial values from the returned rows. You
> may need to query v$session again and wait some time to let the sessions
> really disappear. You can wait in PL/SQL by calling procedure
> dbms_lock.sleep(seconds).
>
> Not my business but just wondering why you want to drop and recreate
users?
> And why quota on tablespace TEMP? Isn't TEMP a temporary tablespace? If
not,
> change it, If it is: no quota needed because its temp.
> The resource role under water grants "unlimited tablespace" priv to the
user
> so the quota you set are effectively useless. That's why I always revoke
> unlimited tablespace immediately after granting resource role.
>
>
Received on Thu Apr 17 2003 - 19:06:29 CDT