No. 8: Reset a User's Password

This is the eighth of Kevin Loney's "10 Scripts Every DBA Should Have." Watch for a complete article on how to use these scripts in a future issue of Oracle Magazine. There are other valuable scripts as well, but these ten--covering backup/recovery, space management, account management, maintenance, and tuning--provide basic guideposts to start from during your administration activities.

Don't leave $ORACLE_HOME without them!

The script below generates an ALTER USER command that will reset a user's password (the user whose username is passed to the script as &&1). If you spool the output of this command, you will be able to reset the user's password without ever having to know what the password was. You can change the user's password to anything you like, use the account for testing purposes, and then reset the password when your testing is complete. For a detailed discussion, see Chapter 9 of the Oracle DBA Handbook.

select 'ALTER USER &&1 IDENTIFIED BY VALUES '
 ||''''||Password||''''||';'
  from DBA_USERS where Username = UPPER('&&1');