1
SELECT GRANTEE,
PRIVILEGE,
RANK()  OVER (PARTITION BY GRANTEE  ORDER BY COUNT(PRIVILEGE) DESC)
FROM DBA_TAB_PRIVS WHERE GRANTEE  IN(
'SYSTEM',
                               'OUTLN',
                               'SCOTT',
                               'HR',
                               'DBSNMP',
                               'AQADM',
                               'ANONYMOUS',
                               'MDSYS',
                               'ANONYMOUS',
                               'CTXSYS',
                               'DIP',
                               'DBSNMP',
                               'DMSYS',
                               'EXFSYS',
                               'MDDATA',
                               'MDSYS',
                               'MGMT_VIEW',
                               'OLAPSYS',
                               'ORDPLUGINS',
                               'ORDSYS',
                               'OUTLN',
                               'SCOTT',
                               'SI_INFORMTN_SCHEMA',
                               'SYSMAN',
                               'WK_TEST',
                               'WKPROXY',
                               'WKSYS'')
GROUP BY   GRANTEE, PRIVILEGE
UNION ALL
SELECT GRANTEE,
PRIVILEGE,
RANK()  OVER (PARTITION BY GRANTEE  ORDER BY COUNT(PRIVILEGE) DESC)
FROM DBA_SYS_PRIVS WHERE GRANTEE  IN/NOT IN(
'SYSTEM',
                               'OUTLN',
                               'SCOTT',
                               'HR',
                               'DBSNMP',
                               'AQADM',
                               'ANONYMOUS',
                               'MDSYS',
                               'ANONYMOUS',
                               'CTXSYS',
                               'DIP',
                               'DBSNMP',
                               'DMSYS',
                               'EXFSYS',
                               'MDDATA',
                               'MDSYS',
                               'MGMT_VIEW',
                               'OLAPSYS',
                               'ORDPLUGINS',
                               'ORDSYS',
                               'OUTLN',
                               'SCOTT',
                               'SI_INFORMTN_SCHEMA',
                               'SYSMAN',
                               'WK_TEST',
                               'WKPROXY',
                               'WKSYS'')
GROUP BY   GRANTEE, PRIVILEGE ;
 
2
SELECT DISTINCT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS
WHERE GRANTEE  IN(
'SYSTEM',
                               'OUTLN',
                               'SCOTT',
                               'HR',
                               'DBSNMP',
                               'AQADM',
                               'ANONYMOUS',
                               'MDSYS',
                               'ANONYMOUS',
                               'CTXSYS',
                               'DIP',
                               'DBSNMP',
                               'DMSYS',
                               'EXFSYS',
                               'MDDATA',
                               'MDSYS',
                               'MGMT_VIEW',
                               'OLAPSYS',
                               'ORDPLUGINS',
                               'ORDSYS',
                               'OUTLN',
                               'SCOTT',
                               'SI_INFORMTN_SCHEMA',
                               'SYSMAN',
                               'WK_TEST',
                               'WKPROXY',
                               'WKSYS')
UNION ALL
SELECT DISTINCT GRANTEE,PRIVILEGE FROM DBA_TAB_PRIVS
WHERE GRANTEE  IN/NOT IN(
'SYSTEM',
                               'OUTLN',
                               'SCOTT',
                               'HR',
                               'DBSNMP',
                               'AQADM',
                               'ANONYMOUS',
                               'MDSYS',
                               'ANONYMOUS',
                               'CTXSYS',
                               'DIP',
                               'DBSNMP',
                               'DMSYS',
                               'EXFSYS',
                               'MDDATA',
                               'MDSYS',
                               'MGMT_VIEW',
                               'OLAPSYS',
                               'ORDPLUGINS',
                               'ORDSYS',
                               'OUTLN',
                               'SCOTT',
                               'SI_INFORMTN_SCHEMA',
                               'SYSMAN',
                               'WK_TEST',
                               'WKPROXY',
                               'WKSYS'')
ORDER BY GRANTEE
3:

select GRANTEE,PRIVILEGE from (
select distinct   GRANTEE,
PRIVILEGE  from (
SELECT  distinct GRANTEE,
PRIVILEGE,
RANK()  OVER (PARTITION BY GRANTEE  ORDER BY COUNT(PRIVILEGE) DESC)
FROM DBA_TAB_PRIVS WHERE GRANTEE  IN(
SELECT USERNAME
    FROM DBA_USERS D
   WHERE USERNAME NOT IN
         (SELECT NAME
            FROM (SELECT SCHEMA#
                    FROM SYS.REGISTRY$
                  UNION ALL
                  SELECT B.SCHEMA#
                    FROM SYS.REGISTRY$ A, SYS.REGISTRY$SCHEMAS B
                   WHERE A.CID = B.CID) A,
                 SYS.USER$ B
           WHERE A.SCHEMA# = B.USER#)
  AND    D.default_tablespace NOT IN('SYSAUX','SYSTEM','USERS')
  )
GROUP BY   GRANTEE, PRIVILEGE
UNION
SELECT distinct  GRANTEE,
PRIVILEGE,
RANK()  OVER (PARTITION BY GRANTEE  ORDER BY COUNT(PRIVILEGE) DESC)
FROM DBA_SYS_PRIVS WHERE GRANTEE  IN(
SELECT USERNAME
    FROM DBA_USERS D
   WHERE USERNAME NOT IN
         (SELECT NAME
            FROM (SELECT SCHEMA#
                    FROM SYS.REGISTRY$
                  UNION ALL
                  SELECT B.SCHEMA#
                    FROM SYS.REGISTRY$ A, SYS.REGISTRY$SCHEMAS B
                   WHERE A.CID = B.CID) A,
                 SYS.USER$ B
           WHERE A.SCHEMA# = B.USER#)
  AND    D.default_tablespace NOT IN('SYSAUX','SYSTEM','USERS'))
GROUP BY   GRANTEE, PRIVILEGE ))
GROUP BY   GRANTEE, PRIVILEGE
order by 1