|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
prompt Database User Password Expired Check prompt _______________________________________________________ prompt col ACCOUNT_STATUS for a18 col LIMIT for a15 col LAST_PWD_CHG_DT for a20 col EXPIRED_PWD_DT for a20 select u.USERNAME,u.ACCOUNT_STATUS,u.LOCK_DATE,u.EXPIRY_DATE,u.CREATED,u1.ptime as "LAST_PWD_CHG_DT", P.LIMIT , decode ( P.LIMIT , 'UNLIMITED','9999/12/31' , 'DEFAULT', to_char(u1.ptime+180,'YYYY/MM/DD' ) , to_char(u1.ptime+to_number(P.LIMIT),'YYYY/MM/DD' ) ) as "EXPIRED_PWD_DT" from dba_users u,sys.user$ u1 , dba_profiles p where u.username=u1.name and u.profile = p.profile and username not in ('SYSAUX','SYSMAN','XDB','HR','ODM','OUTLN','OE','SH','PM','IX','WK_TEST','PERFSTAT','DBSNMP','OLAPSYS','QS_CS','QS_CB','QS_CBADM','QS_OS','QS_WS','QS','QS_ADM','ODM_MTR' ,'WKPROXY','QS_ES','ANONYMOUS','WKSYS','WMSYS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','BI','CTXSYS','DIP','EXFSYS','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OWBSYS','OWBSYS_AUDIT','SCOTT','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL') and p.RESOURCE_NAME='PASSWORD_LIFE_TIME' / |