SELECT  DECODE(COUNT(*),0,'Role is not granted','Role is granted.') "And the answer is"
  FROM  DBA_ROLE_PRIVS
  WHERE GRANTED_ROLE = 'role-in-question'
  START WITH GRANTEE = 'user_name'
  CONNECT BY GRANTEE = PRIOR GRANTED_ROLE
/

SELECT  DISTINCT GRANTEE
  FROM  DBA_ROLE_PRIVS
  WHERE CONNECT_BY_ISLEAF = 1
  START WITH GRANTED_ROLE = 'role-in-question'
  CONNECT BY GRANTED_ROLE = PRIOR GRANTEE
/

 SELECT  GRANTEE
   FROM  DBA_ROLE_PRIVS
   WHERE CONNECT_BY_ISLEAF = 1
   START WITH GRANTED_ROLE = 'role-in-question'
   CONNECT BY GRANTED_ROLE = PRIOR GRANTEE
MINUS
 SELECT  ROLE
   FROM  DBA_ROLES
/

create role test1;

grant create any table to test1;

create role test2;

grant test1 to test2;

create role test3;

grant test2 to test3;

create role test4;

grant connect to test4;

create role test5;

grant test1, test2, test3 to test5;

create role test6;

grant test3, test5 to test6;

select * from role_role_privs
start with granted_role = 'TEST1'
  connect by prior role = granted_role;

create role test1;

grant create any table to test1;

create role test2;

grant test1, create any directory to test2;

create role test3;

grant test2, drop any directory to test3;

create role test4;

grant connect to test4;

create role test5;

grant test1, test2, test3 to test5;

create role test6;

grant test3, test5 to test6;

create user scott identified by tiger;

grant test6 to scott;

create or replace procedure get_privs (pv_grantee in varchar2,lv_tabstop in out number) is
        --
        lv_tab varchar2(50):='';
        lv_loop number;
        --
        cursor c_main (cp_grantee in varchar2) is
        select  'ROLE' typ,
            grantee grantee,
            granted_role priv,
            admin_option ad,
            '--' tabnm,
            '--' colnm,
            '--' owner
        from    dba_role_privs
        where   grantee=cp_grantee
        union
        select  'SYSTEM' typ,
            grantee grantee,
            privilege priv,
            admin_option ad,
            '--' tabnm,
            '--' colnm,
            '--' owner
        from    dba_sys_privs
        where   grantee=cp_grantee
        union
        select  'TABLE' typ,
            grantee grantee,
            privilege priv,
            grantable ad,
            table_name tabnm,
            '--' colnm,
            owner owner
        from    dba_tab_privs
        where   grantee=cp_grantee
        union
        select  'COLUMN' typ,
            grantee grantee,
            privilege priv,
            grantable ad,
            table_name tabnm,
            column_name colnm,
            owner owner
        from    dba_col_privs
        where   grantee=cp_grantee
        order by 1;
    begin
        lv_tabstop:=lv_tabstop+1;
        for lv_loop in 1..lv_tabstop loop
            lv_tab:=lv_tab||chr(9);
        end loop;
        for lv_main in c_main(pv_grantee) loop
            if lv_main.typ='ROLE' then
                dbms_output.put_line(lv_tab||'ROLE => '
                ||lv_main.priv||' which contains =>'); 
                get_privs(lv_main.priv,lv_tabstop);
            elsif lv_main.typ='SYSTEM' then
                dbms_output.put_line(lv_tab||'SYS PRIV => '
                    ||lv_main.priv
                    ||' grantable => '||lv_main.ad);
            elsif lv_main.typ='TABLE' then
                dbms_output.put_line(lv_tab||'TABLE PRIV => '
                    ||lv_main.priv
                    ||' object => '
                    ||lv_main.owner||'.'||lv_main.tabnm
                    ||' grantable => '||lv_main.ad);
            elsif lv_main.typ='COLUMN' then
                dbms_output.put_line(lv_tab||'COL PRIV => '
                    ||lv_main.priv
                    ||' object => '||lv_main.tabnm
                    ||' column_name => '
                    ||lv_main.owner||'.'||lv_main.colnm
                    ||' grantable => '||lv_main.ad);
            end if;
        end loop;
        lv_tabstop:=lv_tabstop-1;
        lv_tab:='';
    exception
        when others then
            dbms_output.put_line('ERROR (get_privs) => '||sqlcode);
            dbms_output.put_line('MSG (get_privs) => '||sqlerrm);
    end get_privs;
    /


declare
a number:=0;
begin
get_privs('SCOTT',a);
end;
/

SQL> def 1='TEST'
SQL> col ord noprint
SQL> with 
  2    roles as (
  3      select granted_role role
  4      from dba_role_privs
  5      connect by prior granted_role = grantee
  6      start with grantee = '&1'
  7      group by granted_role
  8      order by min(level) desc
  9    )
 10  select 1 ord, 'create role '||role||';' statement
 11  from roles
 12  union all
 13  select 2 ord, 'grant '||p.privilege||' to '||p.grantee||';'
 14  from roles r, dba_sys_privs p
 15  where p.grantee = r.role
 16  union all
 17  select 3 ord, 'grant '||p.granted_role||' to '||p.grantee||';'
 18  from roles r, dba_role_privs p
 19  where p.grantee = r.role
 20  union all
 21  select 4 ord, 'grant '||p.privilege||' to &1;'
 22  from dba_sys_privs p
 23  where p.grantee = '&1'
 24  union all
 25  select 5 ord, 'grant '||p.granted_role||' to &1;'
 26  from dba_role_privs p
 27  where p.grantee = '&1'
 28  order by 1, 2
 29  /
STATEMENT
---------------------------------------------------------------------------------
create role TEST1;
create role TEST2;
create role TEST3;
create role TEST5;
create role TEST6;
grant CREATE ANY DIRECTORY to TEST2;
grant CREATE ANY TABLE to TEST1;
grant DROP ANY DIRECTORY to TEST3;
grant TEST1 to TEST2;
grant TEST1 to TEST5;
grant TEST2 to TEST3;
grant TEST2 to TEST5;
grant TEST3 to TEST5;
grant TEST3 to TEST6;
grant TEST5 to TEST6;
grant TEST6 to TEST;

SQL> def 1='TEST'
SQL> col ord noprint
SQL> with 
  2    roles as (
  3      select granted_role role
  4      from dba_role_privs
  5      connect by prior granted_role = grantee
  6      start with grantee = '&1'
  7      group by granted_role
  8      order by min(level) desc
  9    )
 10  select 1 ord, 'create role '||role||';' statement
 11  from roles
 12  union all
 13  select 2 ord, 'grant '||p.privilege||' to '||p.grantee||';'
 14  from roles r, dba_sys_privs p
 15  where p.grantee = r.role
 16  union all
 17  select 3 ord, 'grant '||wm_concat(p.granted_role)||' to '||p.grantee||';'
 18  from roles r, dba_role_privs p
 19  where p.grantee = r.role
 20  group by p.grantee
 21  union all
 22  select 4 ord, 'grant '||wm_concat(p.granted_role)||' to '||p.grantee||';'
 23  from dba_role_privs p
 24  where p.grantee = '&1'
 25  group by p.grantee
 26  union all
 27  select 5 ord, 'grant '||p.privilege||' to '||p.grantee||';'
 28  from dba_sys_privs p
 29  where p.grantee = '&1'
 30  order by 1, 2
 31  /
STATEMENT
------------------------------------------------------------------------------
create role TEST1;
create role TEST2;
create role TEST3;
create role TEST5;
create role TEST6;
grant CREATE ANY DIRECTORY to TEST2;
grant CREATE ANY TABLE to TEST1;
grant DROP ANY DIRECTORY to TEST3;
grant TEST1 to TEST2;
grant TEST1,TEST2,TEST3 to TEST5;
grant TEST2 to TEST3;
grant TEST3,TEST5 to TEST6;
grant TEST6 to TEST;