switch user ("su" like)

Créer un script "su.sql" :

whenever sqlerror exit
column password new_value pw

-- test access to dba_users and if the user exists
declare
   l_passwd varchar2(45);
begin
   select password into l_passwd
      from sys.dba_users
      where username = upper('&1');
end;
/

-- select password in variable pw
select password
   from sys.dba_users
   where username = upper( '&1' );
/

-- change the password for "Hello"
alter user &1 identified by Hello;
connect &1/hello

-- once connected, change password back
alter user &1 identified by values '&pw';
show user
whenever sqlerror continue