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;
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;
No comments:
Post a Comment