some kind of ' deagg ' function in oracle

 Convert comma separated values in a column into rows and Joi... - Ask TOM (oracle.com)

select
      t.r_id,
      t.r_name,
      t.r_role,
      regexp_substr(t.R_Site, '[^,]+', 1, commas.column_value)  as site
   from
      MY_TABLE t,
      table(cast(multiset(select level from dual connect by  level <= length (regexp_replace(t.R_Site, '[^,]+'))  + 1) as sys.OdciNumberList)) commas
    order by 1,2,3,4;