How do I reset a password for a user in Oracle?
How do I restore a user password if the account has expired?

-- To list all expired accounts

SET LINES 300
SET PAGES 999
COL USERNAME FOR A10 
COL ACCOUNT_STATUS FOR A17
COL LOCK_DATE FOR A10 
COL EXPIRY_DATE FOR A11 
COL PROFILE  FOR A8
COL RESOURCE_TYPE FOR A13
COL RESOURCE_NAME FOR A19 
COL LIMIT FOR A10 

SELECT U.USERNAME, 
       U.ACCOUNT_STATUS,
       U.LOCK_DATE,
       U.EXPIRY_DATE, 
       U.PROFILE,
       P.RESOURCE_NAME,
       P.RESOURCE_TYPE,
       P.LIMIT
FROM DBA_USERS U,
     DBA_PROFILES P
WHERE
     U.ACCOUNT_STATUS LIKE '%EXPIRED%' AND
     U.PROFILE = P.PROFILE AND 
     P.RESOURCE_NAME = 'PASSWORD_LIFE_TIME';

USERNAME   ACCOUNT_STATUS    LOCK_DATE  EXPIRY_DATE PROFILE  RESOURCE_NAME       RESOURCE_TYPE LIMIT
---------- ----------------- ---------- ----------- -------- ------------------- ------------- ----------
USRDATA    EXPIRED(GRACE)               22-NOV-17   DEFAULT  PASSWORD_LIFE_TIME  PASSWORD      180

Generate a SQL command to reset a user's password to the same value. There are different commands for different database releases

-- A query to generate a password reset command for all expired accounts in 11gR2 

COL "PASSWORD RESET" FOR A65 
SELECT 'ALTER USER ' || P.NAME || ' IDENTIFIED BY VALUES ' || q'[']' || P.PASSWORD || q'[';]' PASSWORD_RESET
FROM USER$ P,
     DBA_USERS U
WHERE 
     P.NAME = U.USERNAME AND 
     U.ACCOUNT_STATUS LIKE '%EXPIRED%';
     
PASSWORD_RESET
--------------------------------------------------------------------------------
ALTER USER USRDATA IDENTIFIED BY VALUES '5AECD6C05248729F';

-- A query to generate a password reset command for all expired accounts in 12c and higher 

SET LINES 200 
SET PAGES 999
COL PASSWORD_RESET FOR A200 WORD_WRAPPED

SELECT 'ALTER USER ' || P.NAME || ' IDENTIFIED BY VALUES ' || q'[']' || P.SPARE4 || q'[';]' PASSWORD_RESET
FROM USER$ P,
     DBA_USERS U
WHERE 
     P.NAME = U.USERNAME AND 
     U.ACCOUNT_STATUS LIKE '%EXPIRED%';
     

PASSWORD_RESET
--------------------------------------------------------------------------------------------------------------
ALTER USER USRDATA IDENTIFIED BY VALUES 'S:5052C43F5D3FFA43C5775153A8C...0240EACB73048F94F3737541F2D9812CFB';

Another way to get the current hash value of the user's password is to use the DBMS_METADATA.GET_DDL function.

SET PAGES 0
SET LINES 3000
SET LONG 2000000000
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON 
SET SERVEROUTPUT ON SIZE 1000000
COLUMN RESET_PASSWORD FORMAT a2500 WORD_WRAPPED

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', FALSE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);

-- Get user DDL 

SELECT DBMS_METADATA.GET_DDL('USER','&USERNAME') RESET_PASSWORD FROM DUAL;

-- Query result 

Enter value for username: USRDATA
old   1: SELECT DBMS_METADATA.GET_DDL('USER','&USERNAME') RESET_PASSWORD FROM DUAL
new   1: SELECT DBMS_METADATA.GET_DDL('USER','USRDATA') RESET_PASSWORD FROM DUAL

CREATE USER "USRDATA" IDENTIFIED BY VALUES 'S:5052C43F...248729F' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP";

Set a new password for the user's account with the ALTER USER … IDENTIFIED BY … statement. For instance

ALTER USER SMITH IDENTIFIED BY SMITH;

CONNECT SMITH/SMITH
Connected.

SHO USER
USER is "SMITH"

Written At

25 MAY 202118:00

Red Hat Release

7.x x64

Database Release

11.2.0.4 - 19.13.0.0.0