How to get Unique Key and Not Null column Name in Oracle11g?

Question

For constraint validation i want to read unique key and not null column from oracle11g, I could able to retrieve same from Mysql but don't know how to do it in Oracle 11g

Answer 1

You need to join [DBA|ALL|USER]_CONSTRAINTS view with [DBA|ALL|USER]_CONS_COLUMNS view.

NOT NULL constraint is type C. UNIQUE constraint is type U.

SELECT a.table_name,
  b.column_name,
  a.constraint_type
FROM dba_constraints a
JOIN dba_cons_columns b
ON a.owner             = b.owner
AND a.constraint_name  = b.constraint_name
AND a.table_name       = b.table_name
AND a.owner            ='<user_name>'
AND a.constraint_type IN ('U', 'C');