rem -----------------------------------------------------------------------
rem Filename:   su.sql
rem Purpose:    Switch from one database user to another (local db only)
rem Syntax:     @su <user>
rem Notes:      Require ALTER USER and SELECT ON DBA_USERS privileges
rem Date:       12-Apr-1998
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set termout off head off
col nl newline
spool su.tmp

select 'alter user &&1 identified by &&1;' nl,
       'connect &&1/&&1' nl,
       'alter user &&1 identified by values '''||u.password||''';' nl
from   sys.dba_users u
where  u.username = upper('&&1')
  and  u.username <> user
/

spool off

@su.tmp

REM Delete the file. Use "rm" on Unix, "del" for Win/Dos
! rm su.tmp
! del su.tmp

set termout on

select 'Connected as '||USER||' on '||global_name||'.'
from   global_name;

set termout on head on