select * from user_role_privs urp
where urp.GRANTED_ROLE = 'MY_COOL_ROLE'

USERNAME                       GRANTED_ROLE                   ADMIN_OPTION DEFAULT_ROLE OS_GRANTED
------------------------------ ------------------------------ ------------ ------------ ----------
A                              MY_COOL_ROLE                  NO           YES          NO

begin
  if dbms_session.is_role_enabled('MY_COOL_ROLE') then
    dbms_output.put_line('ЕСТЬ ТАКАЯ РОЛЬ');
  else
    dbms_output.put_line('НЕТ ТАКОЙ РОЛИ');   
  end if;
end;

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE	11.2.0.2.0	Production
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

alter user ИМЯ_ПОЛЬЗОВАТЕЛЯ default role ALL;