select grantee
from dba_role_privs r, dba_users u
where granted_role ='CONNECT' and
   r.grantee = u.username and
   u.account_status not like '%LOCKED%' and
   grantee not in ('SYS','SYSTEM','DBSNMP')
union
select grantee
from dba_role_privs r, dba_users u
where r.grantee = u.username and
  u.account_status not like '%LOCKED%' and
  grantee not in ('SYS','SYSTEM','DBSNMP') and
  granted_role in
    (select grantee
     from dba_role_privs
     where granted_role = 'CONNECT' and
       grantee <> 'DBA');