Sometimes it is useful to do the following:
1) Save an Oracle user's password to another file
2) Change the user's password to a temporary value so that you can login to Oracle as this user
3) Return the user's password to the original value
To do this, run the BecomeAnotherUser.sql file as follows:
1) Login to SQL*Plus, as the SYSTEM user
2) Run BecomeAnotherUser.sql by issuing this command in SQL*Plus:
SQL> @BecomeAnotherUser
This script will do the following:
1) Prompt for the Oracle Username whose password you will be resetting
2) Creates the BecomeAnotherUserReset.sql file that will be used to reset the user's password to the original value
3) Changes the user's password to TEMP
4) Pause and wait for you to hit [Enter] --- this gives you time to login to another window as the other user
5) Reverts the users password back to the original value
Here is the BecomeAnotherUser.sql script:
set termout on
set feedback on
accept name prompt 'Enter the Oracle UserName: '
set pagesize 0
set feedback off
set verify off
set echo off
set termout off
spool BecomeAnotherUserReset.sql
select 'alter user &&name identified by values '||''''|| password ||''''||';'
from dba_users WHERE username = upper ('&&name');
spool off
alter user &&name identified by temp;
set termout on
set feedback on
prompt Password of &&name changed to temp
prompt
accept pausevar prompt 'Press <Enter> to change it back to the original value...'
@BecomeAnotherUserReset.sql