Monday, June 20, 2016

How to list all users and their number of owned objects


Join dba_users with dba_objects using a correlated subquery, and you'll get all the schemas, both those which own objects and those who don't:
set lines 200
col username format a20
col created format a30
col "Num_obj" format 999999
SELECT u.username,u.created,(SELECT COUNT(*) FROM dba_objects o WHERE o.owner = u.username)"Num_obj"
FROM dba_users u
-- to leave out all oracle maintained schemas, comment out the following
-- WHERE  u.oracle_maintained = 'N'
ORDER BY 3 asc;

Example output:
USERNAME             CREATED                        Num_obj
-------------------- ------------------------------ -------
ANONYMOUS            14.06.2016                           0
ADDM_USER            14.06.2016                           0
GSMUSER              14.06.2016                           0
SYSDG                14.06.2016                           0
DIP                  14.06.2016                           0
SYSBACKUP            14.06.2016                           0
SCOTT                14.06.2016                           0
USER1                20.06.2016                           0
USER2                20.06.2016                           0
SYSKM                14.06.2016                           0
XS$NULL              14.06.2016                           0
GSMCATUSER           14.06.2016                           0
APPQOSSYS            14.06.2016                           5
ORACLE_OCM           14.06.2016                           6
OUTLN                14.06.2016                          10
AUDSYS               14.06.2016                          12
OJVMSYS              14.06.2016                          16
USER3                20.06.2016                          34
DBSNMP               14.06.2016                          55
USER4                20.06.2016                          95
WMSYS                14.06.2016                         389
CTXSYS               14.06.2016                         409
SYSTEM               14.06.2016                         641
XDB                  14.06.2016                         961
SYS                  14.06.2016                       42173

If you have a large number of accounts that own no objects at all, you may want to exclude them. Do so by checking for the existence of the particular account in dba_objects.
If there are any rows at all in there, the account owns at least 1 object. Make sure to select a literal, not a value from the database. This is good practice; performance is not affected:
SELECT u.username,u.created,(SELECT COUNT(*) FROM dba_objects o WHERE o.owner = u.username )"Num_obj"
FROM dba_users u
WHERE EXISTS (SELECT 1 FROM dba_objects o WHERE o.owner = u.username)
ORDER BY 3 asc;

No comments:

Post a Comment