Query : Users and Application Roles

Tables used:
per_user_roles pur
per_roles_dn prd
per_users pu
PER_PERSON_NAMES_F ppnf


Select distinct prd.role_common_name, pu.username, ppnf.first_name, ppnf.last_name

from
per_user_roles pur,
per_roles_dn prd,
per_users pu,
PER_PERSON_NAMES_F ppnf

where pur.role_id = prd.role_id
and prd.role_common_name = 'XXX_ROLE_NAME'
and pu.user_id = pur.user_id
and ppnf.person_id = pu.person_id



From:  https://rpforacle.blogspot.com/2019/03/user-and-roles-query-in-oracle-cloud.html

SELECT pu.user_id,
         pu.username,
         ppnf.full_name,
         prdt.role_id,
         prdt.role_name,
         prd.role_common_name,
         prdt.description,
         TO_CHAR (pur.start_date, 'DD-MON-YYYY') role_start_date,
         TO_CHAR (pur.end_date, 'DD-MON-YYYY') role_end_date,
         prd.abstract_role,
         prd.job_role,
         prd.data_role,
         prd.duty_role,
         prd.active_flag
    FROM per_user_roles    pur,
         per_users         pu,
         per_roles_dn_tl   prdt,
         per_roles_dn      prd,
         per_person_names_f ppnf
   WHERE     1 = 1
         AND pu.user_id = pur.user_id
         AND prdt.role_id = pur.role_id
         AND prdt.language = USERENV ('lang')
         AND prdt.role_id = prd.role_id
         AND NVL (pu.suspended, 'N') = 'N'
   AND pu.username =:p_username
         AND ppnf.person_id = pu.person_id
         AND ppnf.name_type = 'GLOBAL'
         AND pu.active_flag = 'Y'
         AND NVL (pu.start_date, SYSDATE) <= SYSDATE
         AND NVL (pu.end_date, SYSDATE) >= SYSDATE
ORDER BY pu.username, prdt.role_name;