-- 12c
-- spare4
set lines 1000
set pages 100
set trimspool on
set trimout on
set wrap off

col "USERNAME" for a10
col "ACCOUNT_STATUS" for a5
col "PASSWORD" for a32

SELECT 
    TO_CHAR(D.EXPIRY_DATE, 'YYYYMMDD'),    
    D.USERNAME,
    D.ACCOUNT_STATUS, 
    U.PASSWORD,
    'ALTER USER '||D.USERNAME||' IDENTIFIED BY VALUES '''||U.SPARE4||''';' AS CHANGE_SQL
FROM SYS.USER$ U
    JOIN DBA_USERS D
        ON D.USERNAME = U.NAME
        --AND NVL(TRUNC(D.EXPIRY_DATE),TRUNC(SYSDATE-1)) BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE+15)
        --and D.USERNAME like  'PROD_%'
        and D.ACCOUNT_STATUS = 'EXPIRED'
WHERE 1=1
;