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