set linesize 132 set pagesize 999 col username for a23 col dba for a3 col status for a7 col default_tablespace for a20 col profile for a18 col pwd_verify format a20 col plt for a3 col fla for a3 col rum for a3 col pgt for a3 col pwd_lok for a7 with profile_detail as ( select dbp.profile, dbp.resource_name, decode(dbp.limit,'DEFAULT',def.limit,'NULL','','UNLIMITED','',dbp.limit) limit from dba_profiles dbp, ( select def.resource_name, decode(def.limit,'NULL','','UNLIMITED','',def.limit) limit from dba_profiles def where profile = 'DEFAULT' ) def where 1=1 and dbp.resource_name = def.resource_name order by 2,1 ) select dbu.username, decode(dba.grantee,null,'NO','YES') dba, replace( replace( replace( replace(dbu.account_status,'LOCKED','LOK'), 'EXPIRED','EXP'), '(GRACE)','(GR)'), ' & ','&' ) status, -- dbu.lock_date, -- dbu.expiry_date, -- dbu.default_tablespace, dbu.profile, pwd.limit "PWD_VERIFY", plt.limit "PLT", fla.limit "FLA", rum.limit "RUM", pgt.limit "PGT", plk.limit "PWD_LOK" from dba_users dbu, ( select grantee from dba_role_privs where granted_role = 'DBA' ) dba, profile_detail pwd, profile_detail plt, profile_detail fla, profile_detail rum, profile_detail pgt, profile_detail plk where 1=1 and pwd.profile=dbu.profile and pwd.resource_name = 'PASSWORD_VERIFY_FUNCTION' and plt.profile=dbu.profile and plt.resource_name = 'PASSWORD_LIFE_TIME' and fla.profile=dbu.profile and fla.resource_name = 'FAILED_LOGIN_ATTEMPTS' and rum.profile=dbu.profile and rum.resource_name = 'PASSWORD_REUSE_MAX' and pgt.profile=dbu.profile and pgt.resource_name = 'PASSWORD_GRACE_TIME' and plk.profile=dbu.profile and plk.resource_name = 'PASSWORD_LOCK_TIME' and dbu.username = dba.grantee(+) order by 1 /