Expired Users

-- 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
;