SELECT r3.grantee, granted_role privilege
FROM dba_role_privs r3
START WITH r3.granted_role IN
(SELECT DISTINCT p4.grantee
FROM dba_role_privs r4, dba_sys_privs p4
WHERE r4.granted_role = p4.grantee
AND p4.privilege =
'UNLIMITED TABLESPACE')
CONNECT BY PRIOR grantee = granted_role)
-- we just whant to see the users not the roles
OR grantee = 'PUBLIC'
UNION ALL
-- list the user with unimited quota on a dedicated tablespace
SELECT username, tablespace_name, 'DBA_TS_QUOTA' privilege
FROM dba_ts_quotas
WHERE max_bytes = -1)
WHERE tablespace_name LIKE UPPER ('SYSTEM')
OR tablespace_name = 'Any Tablespace' AND username = 'TEST';