Table contraints

set linesize 230
set pagesize 9999

COLUMN constraint_name     FORMAT A35                HEADING "Constraint Name"
COLUMN constraint_type     FORMAT A11                HEADING "Constraint Type"
COLUMN column_name         FORMAT A30                HEADING "Column name"
COLUMN search_condition    FORMAT A15                HEADING "Search Condition"
COLUMN r_constraint_name   FORMAT A20                HEADING "R / Constraint Name"
COLUMN delete_rule         FORMAT A11                HEADING "Delete Rule"
COLUMN status                                        HEADING "Status"

BREAK ON constraint_name ON constraint_type

SELECT 
    a.constraint_name
  , DECODE(a.constraint_type
             , 'P', 'Primary Key'
             , 'C', 'Check'
             , 'R', 'Referential'
             , 'V', 'View Check'
             , 'U', 'Unique'
             , a.constraint_type
    ) constraint_type
  , b.column_name
  , a.search_condition
  , NVL2(a.r_owner, a.r_owner || '.' ||  a.r_constraint_name, null) r_constraint_name
  , a.delete_rule
  , a.status
FROM 
    dba_constraints  a
  , dba_cons_columns b
WHERE
      a.owner            = UPPER('&schema_name')
  AND a.table_name       = UPPER('&table_name')
  AND a.constraint_name  = b.constraint_name
  AND b.owner            = UPPER('&schema_name')
  AND b.table_name       = UPPER('&table_name')
ORDER BY
    a.constraint_name
  , b.position
/