Monday, September 10, 2012

LAST_PASSWORD_CHANGE.

set linesize 140
set pagesize 30000
col Creation_Date format a30
col Last_Password_Change format a30

SELECT du.username,
       u.ctime  Creation_Date,
       u.ptime  Last_Password_change,
       Count(*) "OBJECT_COUNT"
FROM   dba_users du,
       sys.user$ u,
       dba_objects a
WHERE  du.username = u.name
       AND a.owner = u.name
       AND du.username NOT IN ( 'SYS', 'SYSTEM', 'DBSNMP', 'OUTLN',
                                'SCOTT', 'ANONYMOUS', 'CTXSYS', 'DIP',
                                'DMSYS', 'EXFSYS', 'MDSYS', 'ORDSYS',
                                'XDB', 'MGMT_VIEW', 'OEMMGR', 'OLAPSYS',
                                'OPS$ORACLE', 'ORACLE_OCM', 'SECADMIN', 'WMSYS',
                                'XS$NULL', 'SYSMAN' )
GROUP  BY du.username,
          u.ctime,
          u.ptime
UNION ALL
SELECT dv.username,
       v.ctime Creation_Date,
       v.ptime Last_Password_change,
       0
FROM   dba_users dv,
       sys.user$ v
WHERE  dv.username = v.name
       AND dv.username NOT IN (SELECT DISTINCT owner
                               FROM   dba_objects)
       AND dv.username NOT IN ( 'SYS', 'SYSTEM', 'DBSNMP', 'OUTLN',
                                'SCOTT', 'ANONYMOUS', 'CTXSYS', 'DIP',
                                'DMSYS', 'EXFSYS', 'MDSYS', 'ORDSYS',
                                'XDB', 'MGMT_VIEW', 'OEMMGR', 'OLAPSYS',
                                'OPS$ORACLE', 'ORACLE_OCM', 'SECADMIN', 'WMSYS',
                                'XS$NULL', 'SYSMAN' )
ORDER  BY 1;