Compare two Oracle database roles
Here is a query that finds duplicate role_tab_privs. It uses Oracle LISTAGG aggregate function to create a list of privileges for each role, owner, table. 

SELECT arole, brole, aowner, atable, aprivlist
FROM 
      --join to each grouped view by role, owner, table
      (
      SELECT A.ROLE AS arole, A.owner AS aowner, A.table_name AS atable,
                  --one field for all privileges concatenated with | separator
                  listagg(a.privilege, ' | ') WITHIN GROUP(ORDER BY a.privilege) AS aprivlist
      FROM role_tab_privs a
      GROUP BY A.ROLE, A.owner, A.table_name
      )
JOIN 
      (
      SELECT b.ROLE as brole, b.owner as bowner, b.table_name as btable,
                  listagg(b.privilege, ' | ') WITHIN GROUP(ORDER BY b.privilege) AS bprivlist
      FROM role_tab_privs b
      GROUP BY b.ROLE, b.owner, b.table_name
      )
  -- the roles cannot be equal but the owner, table, and privileges must be
  ON AROLE != bROLE
 AND aowner = bowner
 AND atable = btable
 AND aprivlist = bprivlist
ORDER BY aowner, atable, arole 
;


Note that the LISTAGG is used only on the privileges within a role, owner, table group. The result of a `LISTAGG' function must fit within the max varchar(4000) or the error:

ORA-01489: result of string concatenation is too long


This query gives a good view of how the table privileges relate.