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

Oracle constraints info user wise ..

set serveroutput on
DECLARE
constraint_text long;
c_name varchar2(100);
c_type varchar2(100);
c_owner varchar2(100);
c_table_name varchar2(100);

cursor CONSTRAINTS is SELECT owner, table_name, search_condition,constraint_name, constraint_type 
FROM dba_constraints where OWNER in ('SCOTT');
BEGIN

OPEN CONSTRAINTS;

LOOP

FETCH CONSTRAINTS INTO c_owner, c_table_name, constraint_text, c_name, c_type;

exit when CONSTRAINTS%NOTFOUND;

IF instr(upper(constraint_text),'IS NOT NULL') = 0 or constraint_text is null THEN
dbms_output.put_line ('==============================================');
dbms_output.put_line ('Table Name = '||c_owner||'.'||c_table_name);
dbms_output.put_line ('Constraint Name = '||c_name);
dbms_output.put_line ('Constraint Name = '||c_name);
dbms_output.put_line ('Constraint Type = '||c_type);
dbms_output.put_line ('Search Condition = '||constraint_text);
END IF;
END LOOP;
CLOSE CONSTRAINTS;
dbms_output.put_line ('==============================================');
END;
/

--- ========================================================

---With column 

set serveroutput on
DECLARE
constraint_text long;
c_name varchar2(100);
c_type varchar2(100);
c_owner varchar2(100);
c_table_name varchar2(100);
b_column_name varchar2(100);

cursor CONSTRAINTS is SELECT c.owner,c.table_name, c.search_condition,c.constraint_name, c.constraint_type,b.column_name FROM dba_constraints c,dba_cons_columns b
where 1=1
and c.constraint_name = b.constraint_name
AND c.owner = b.owner
and c.TABLE_NAME=b.TABLE_NAME
and c.OWNER in ('SCOTT')
order by 2;
BEGIN
OPEN CONSTRAINTS;
LOOP
FETCH CONSTRAINTS INTO c_owner, c_table_name, constraint_text, c_name, c_type,b_column_name ;
exit when CONSTRAINTS%NOTFOUND;
IF instr(upper(constraint_text),'IS NOT NULL') = 0 or constraint_text is null THEN
dbms_output.put_line ('==============================================');
dbms_output.put_line ('Table Name = '||c_owner||'.'||c_table_name);
dbms_output.put_line ('column Name = '||b_column_name);
dbms_output.put_line ('Constraint Name = '||c_name);
dbms_output.put_line ('Constraint Type = '||c_type);
dbms_output.put_line ('Search Condition = '||constraint_text);
END IF;
END LOOP;
CLOSE CONSTRAINTS;
dbms_output.put_line ('==============================================');
END;
/



==============================================
Table Name = SCOTT.DEPT
column Name = DEPTNO
Constraint Name = PK_DEPT
Constraint Type = P
Search Condition =
==============================================
Table Name = SCOTT.EMP
column Name = EMPNO
Constraint Name = PK_EMP
Constraint Type = P
Search Condition =
==============================================
Table Name = SCOTT.EMP
column Name = DEPTNO
Constraint Name = FK_DEPTNO
Constraint Type = R
Search Condition =
==============================================
Table Name = SCOTT.PRIKEY_TEST
column Name = ID
Constraint Name = PK_UNIQUE_TEST
Constraint Type = P
Search Condition =
==============================================

PL/SQL procedure successfully completed.