select * from dba_sys_privs where privilege = 'DBA'
select * from  dba_roles where role = 'DBA'
select * from dba_role_privs where granted_role = 'DBA'

SQL> select * from dba_role_privs where granted_role = 'DBA' ;

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
SYS                            DBA                            YES YES
SYSTEM                         DBA                            YES YES

SQL>

SQL> connect owb_target
Enter password:
Connected.
SQL> select * from session_roles;

no rows selected

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE SYNONYM
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK

PRIVILEGE
----------------------------------------
CREATE PROCEDURE
CREATE TRIGGER
CREATE SNAPSHOT
CREATE ANY DIRECTORY
DROP ANY DIRECTORY
CREATE ANY TYPE
CREATE INDEXTYPE
GLOBAL QUERY REWRITE
CREATE DIMENSION
SELECT ANY DICTIONARY

21 rows selected.

SQL>

SQL> connect scott/tiger
Connected.
SCOTT> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
SELECT ANY DICTIONARY

11 rows selected.

SCOTT> connect michel/michel
Connected.
MICHEL> revoke select any dictionary  from scott;

Revoke succeeded.

MICHEL> connect scott/tiger
Connected.
SCOTT> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

10 rows selected.

SQL> connect owb_target
Enter password:
Connected.
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE SYNONYM
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER
CREATE SNAPSHOT
CREATE ANY DIRECTORY
DROP ANY DIRECTORY
CREATE ANY TYPE
CREATE INDEXTYPE
GLOBAL QUERY REWRITE
CREATE DIMENSION
SELECT ANY DICTIONARY

21 rows selected.

SQL>
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> revoke SELECT ANY DICTIONARY from owb_target ;
revoke SELECT ANY DICTIONARY from owb_target
*
ERROR at line 1:
ORA-01952: system privileges not granted to 'OWB_TARGET'


SQL>

SQL> connect owb_target
Enter password:
Connected.
SQL> select * from session_roles;

no rows selected

SQL>

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> select * from dba_sys_privs where grantee = 'OWB_TARGET' ;

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
OWB_TARGET                     CREATE VIEW                              NO
OWB_TARGET                     CREATE TABLE                             NO
OWB_TARGET                     ALTER SESSION                            NO
OWB_TARGET                     CREATE SESSION                           NO
OWB_TARGET                     CREATE SYNONYM                           NO
OWB_TARGET                     CREATE TRIGGER                           NO
OWB_TARGET                     CREATE ANY TYPE                          NO
OWB_TARGET                     CREATE SEQUENCE                          NO
OWB_TARGET                     CREATE SNAPSHOT                          NO
OWB_TARGET                     CREATE DIMENSION                         NO
OWB_TARGET                     CREATE INDEXTYPE                         NO

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
OWB_TARGET                     CREATE PROCEDURE                         NO
OWB_TARGET                     DROP ANY DIRECTORY                       NO
OWB_TARGET                     DROP PUBLIC SYNONYM                      NO
OWB_TARGET                     CREATE ANY DIRECTORY                     NO
OWB_TARGET                     CREATE DATABASE LINK                     NO
OWB_TARGET                     GLOBAL QUERY REWRITE                     NO
OWB_TARGET                     UNLIMITED TABLESPACE                     NO
OWB_TARGET                     CREATE PUBLIC SYNONYM                    NO
OWB_TARGET                     CREATE PUBLIC DATABASE LINK              NO

20 rows selected.

SQL> select * from dba_role_privs where grantee = 'OWB_TARGET';

no rows selected

SQL>