Below is query to get roles assigned to user(s). You can define username by using p_user_name parameter while running the query, or leave it empty to get all user's roles.
SELECT pu.username
, NAME.full_name
, prdv.role_id
, prdv.role_name
, TO_CHAR (TRUNC (pur.start_date), 'MM/DD/YYYY') role_start_date
, ppnf.effective_end_date
FROM per_user_roles pur
, per_users pu
, per_roles_dn_vl prdv
, per_person_names_f ppnf
WHERE pur.user_id = pu.user_id
AND pu.person_id = NAME.person_id(+)
AND pur.role_id = prdv.role_id
AND NVL (pu.suspended, 'N') = 'N'
AND NAME.name_type = 'GLOBAL'
AND TRUNC (SYSDATE) BETWEEN TRUNC (ppnf.effective_start_date) AND TRUNC (ppnf.effective_end_date)
AND pu.username = nvl(:p_user_name, pu.username)
ORDER BY pu.username
, prdv.role_name
