Oracle role info

column table_name format a20
column privilege format a15
column owner format a10
column role format a30
break on table_name skip 1 noduplicates
spool /tmp/DbaOutPut/grants_recvd.txt
select decode(a.ord,2,' ','') || a.table_name table_name,a.privilege,a.owner,a.role
from (select grantee,2 ord,owner,table_name,privilege,' ' role from dba_tab_privs
where grantee in (select username from dba_users where default_tablespace = 'USERS')
union all
select dr.grantee,2,dt.owner,dt.table_name,dt.privilege,dr.granted_role
from dba_tab_privs dt,dba_role_privs dr
where dt.grantee = dr.granted_role
and dr.default_role = 'YES'
and dr.grantee in (select username from dba_users where default_tablespace = 'USERS')
union all
select distinct grantee,1,' ',grantee,' ',' ' from dba_tab_privs
where grantee in (select username from dba_users where default_tablespace = 'USERS')
union all
select distinct dr.grantee,1,' ',dr.grantee,' ',' ' from dba_tab_privs dt,dba_role_privs dr
where dt.grantee = dr.granted_role
and dr.default_role = 'YES'
and dr.grantee in (select username from dba_users where default_tablespace = 'USERS')) a ------------------
order by grantee,ord,table_name,owner,privilege,role;
spool off
clear breaks
set linesize 80 termout on heading on pagesize 24 timing on feedback 6
set termout on verify on echo on showmode both




TABLE_NAME PRIVILEGE OWNER ROLE
-------------------------------- ---------------------------------------- ------------------------------ ------------------------------
XDB$ACL QUERY REWRITE XDB DBA
XDB$ACL SELECT XDB DBA
XDB$ACL SELECT XDB OLAP_XS_ADMIN
XDB$ACL UPDATE XDB DBA
XDB$ACL UPDATE XDB OLAP_XS_ADMIN
XDB$CHECKOUTS ALTER XDB DBA