BC 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 a6
--col "PASSWORD" FOR a32
col CHANGE_SQL FOR a500
SELECT
    TO_CHAR(D.EXPIRY_DATE, 'YYYYMMDD') AS "DATE"
    ,D.USERNAME
    ,D.ACCOUNT_STATUS 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 = 'SYSTEM'
        AND D.ACCOUNT_STATUS != 'OPEN'
WHERE 1=1
;