Script to check oracle profile parametets including password function details

 WITH P1 AS (

SELECT name,  REGEXP_SUBSTR(SUBSTR(REPLACE(REPLACE(text, chr(10), ''),'=>','='),42), 'chars = ([0-9]+)', 1, 1, NULL, 1) AS PASSWORD_LENGTH  FROM dba_source

WHERE "NAME" in (SELECT REPLACE(limit, chr(10)) FROM dba_profiles WHERE resource_name = 'PASSWORD_VERIFY_FUNCTION')

AND text LIKE '%complexity_check%'

),P2 AS (

SELECT PROFILE,

MAX(DECODE(RESOURCE_NAME,'FAILED_LOGIN_ATTEMPTS',5))            FAILED_LOGIN_ATTEMPTS           ,

MAX(DECODE(RESOURCE_NAME,'PASSWORD_LIFE_TIME',LIMIT))           PASSWORD_LIFE_TIME                      ,

MAX(DECODE(RESOURCE_NAME,'PASSWORD_REUSE_TIME',LIMIT))          PASSWORD_REUSE_TIME                     ,

MAX(DECODE(RESOURCE_NAME,'PASSWORD_REUSE_MAX',LIMIT))           PASSWORD_REUSE_MAX                      ,

MAX(DECODE(RESOURCE_NAME,'PASSWORD_VERIFY_FUNCTION',LIMIT)) PASSWORD_VERIFY_FUNCTION    ,

MAX(DECODE(RESOURCE_NAME,'PASSWORD_LOCK_TIME',LIMIT))           PASSWORD_LOCK_TIME                      ,

MAX(DECODE(RESOURCE_NAME,'PASSWORD_GRACE_TIME',LIMIT))          PASSWORD_GRACE_TIME             ,

MAX(DECODE(RESOURCE_NAME,'INACTIVE_ACCOUNT_TIME',LIMIT))        INACTIVE_ACCOUNT_TIME           ,

MAX(DECODE(RESOURCE_NAME,'PASSWORD_ROLLOVER_TIME',LIMIT))       PASSWORD_ROLLOVER_TIME          ,

MAX(DECODE(RESOURCE_NAME,'SESSIONS_PER_USER',LIMIT))            SESSIONS_PER_USER

FROM dba_profiles

-- where  RESOURCE_TYPE='PASSWORD'

GROUP BY PROFILE

), P3 AS (

SELECT USERNAME,  PROFILE, ORACLE_MAINTAINED,account_status FROM DBA_USERS

)

SELECT

                SYS_CONTEXT('USERENV','DB_NAME') DB_NAME

    ,   to_char(sysdate,'DD-MON-YYYY HH24:MI:ss') AS REPORT_DATE

    ,   P2.PROFILE

  ,   P2.FAILED_LOGIN_ATTEMPTS

    ,   P2.PASSWORD_LOCK_TIME

  ,   P2.PASSWORD_LIFE_TIME

      ,       P2.PASSWORD_REUSE_MAX

      ,       P2.PASSWORD_REUSE_TIME

      ,       P2.PASSWORD_GRACE_TIME

      ,       P2.PASSWORD_VERIFY_FUNCTION

      ,       P2.SESSIONS_PER_USER

      ,       P2.INACTIVE_ACCOUNT_TIME

      ,       P1.PASSWORD_LENGTH

      ,       P3.ORACLE_MAINTAINED

        ,       LISTAGG(P3.USERNAME,', ') AS AFFECTED_USERS

    , p3.account_status ACCOUNT_STATUS

FROM P2

LEFT JOIN  P1 ON P1.NAME = P2.PASSWORD_VERIFY_FUNCTION

LEFT JOIN P3 ON P3.PROFILE = P2.PROFILE

WHERE P3.USERNAME IS NOT NULL and P3.ORACLE_MAINTAINED ='N'

GROUP BY

        P2.PROFILE,  P2.FAILED_LOGIN_ATTEMPTS,  P2.PASSWORD_LIFE_TIME,  P2.PASSWORD_REUSE_TIME,  P2.PASSWORD_REUSE_MAX

    ,   P2.PASSWORD_VERIFY_FUNCTION,  P1.PASSWORD_LENGTH,  P2.PASSWORD_LOCK_TIME     ,   P2.INACTIVE_ACCOUNT_TIME

    ,   P2.PASSWORD_GRACE_TIME    ,   P3.ORACLE_MAINTAINED , P2.SESSIONS_PER_USER  ,p3.account_status

ORDER BY

       P2.PROFILE , P3.ACCOUNT_STATUS -- P3.ORACLE_MAINTAINED

;