Oracle: SYS.CDEF$

select
  decode(type#,
    12, 'USER LOG GROUP',
    14, 'PRIMARY KEY LOGGING',
    15, 'UNIQUE KEY LOGGING',
    16, 'FOREIGN KEY LOGGING',
    17, 'ALL COLUMN LOGGING',
        '???'
    ) type,
    case bitand(defer,64) when 64 then 'ALWAYS'         else  'CONDITIONAL' end,
    case bitand(defer, 8) when  8 then 'GENERATED NAME' else  'USER NAME'   end
from
   sys.cdef$;