Re: Disconnect connected sessions

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 - 15:34:46 CDT