A DBA frequently needs to become another user to test something or verify a problem. Short of having to gain acess to that users password, we are asked can I su to that account, sort of like root does on unix.
This is an 'su.sql' script I use:
whenever sqlerror exit
column password new_value pw
select password into l_passwd
where username = upper('&1');
where username = upper( '&1' )
alter user &1 identified by Hello;
alter user &1 identified by values '&pw';
whenever sqlerror continue
it starts by testing your access to the sys.dba_users table -- if that fails -- it exits SQLPlus. If zero rows returned -- it exits SQLPlus.
It then selects the 'password' from the dba_users table and stuffs it into a macro variable "&pw"
We alter the user you want to become to have a known password (if that fails, we exit).
We 'fix' their password back after loggin in as them....
Note, you need to have access to dba_users and the alter user privelege.