Temporarily changing User's Password

An interesting but undocumented Oracle feature lets you temporarily log in as another user. Logging in as another user is helpful when troubleshooting privilege-related problems. To use this technique you do not even need to know the other user's password.
For example, say that you are the SYSTEM user, and that you need to log in as the COIN_ADMIN user. The first thing to do is to retrieve the COIN_ADMIN user's password. This example shows how to do that:

SQL> SELECT password 
  2  FROM dba_users
  3  WHERE username='COIN_ADMIN';
PASSWORD
------------------------------
B1DB09EA3F74189C

The value that you get for the password when you query DBA_USER is the hexadecimal representation of the encrypted password. You cannot use it to log in. Just save this value so that later you can restore it. The next task is to temporarily change the COIN_ADMIN user's password:

SQL> ALTER USER coin_admin IDENTIFIED BY temp;
User altered.

Now you can log in as

COIN_ADMIN/TEMP
SQL> CONNECT coin_admin/temp@coin
Connected

Once you are logged in as the COIN_ADMIN user, you can change the password back to its original value by issuing this command:
hexadecimal: A base-16 numbering system often used to display an exact representation of data in memory. Hexadecimal numbering goes from 1-9, and then from A-F. The hexadecimal A is equivilant to the decimal value 10. After F, the next hexadecimal value is 10, which is equivilant to the decimal value 16. Hexadecimal digits each represent exactly four binary bits, making hexadecimal a convenient alternative to writing long strings of 1s and 0s.

SQL> ALTER USER coin_admin
2  IDENTIFIED BY VALUES 'B1DB09EA3F74189C';
User altered.

Notice that the hexadecimal string that you saved earlier was used to reset the password back to its original state. The keyword VALUES is an undocumented feature that allows you to store an already encrypted password directly.