Query below lists:

(A) all check constraints on tables accessible to the current user in Oracle database

(B) all check constraints on tables in Oracle database

Query was executed under the Oracle9i Database version.

Query

A. Tables accessible to the current user

select ctr.owner as schema_name,
       ctr.constraint_name,
       ctr.table_name,
       col.column_name,
       ctr.search_condition as constraint,
       ctr.status
from sys.all_constraints ctr
join sys.all_cons_columns col
     on ctr.owner = col.owner
     and ctr.constraint_name = col.constraint_name
     and ctr.table_name = col.table_name
where ctr.constraint_type = 'C'
and ctr.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
   'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 
   'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS', 
   'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
   'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
   'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC','APEX_040200')
order by ctr.owner, ctr.table_name, ctr.constraint_name;

B. If you have privilege on dba_constraints

select ctr.owner as schema_name,
       ctr.constraint_name,
       ctr.table_name,
       col.column_name,
       ctr.search_condition as constraint,
       ctr.status
from sys.dba_constraints ctr
join sys.dba_cons_columns col
     on ctr.owner = col.owner
     and ctr.constraint_name = col.constraint_name
     and ctr.table_name = col.table_name
where ctr.constraint_type = 'C'
and ctr.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS',
   'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
   'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS',
   'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
   'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
   'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC','APEX_040200')
   and column_name != 'LABEL'
order by ctr.owner, ctr.table_name, ctr.constraint_name;

Columns

• schema_name - schema name, owner of the constraint
• constraint_name - name of the constraint
• table_name - name of the table with the constraint
• column_name - name of the column
• constraint - text of the check constraint
• status - status of the constraint (ENABLED/DISABLED)

Rows

• One row represents one constraint in a specific table in a database
• Scope of rows: (A) all check constraints on tables accessible to the current user in Oracle database, (B) all check constraints on tables in Oracle database
• Ordered by schema name, table name, constraint name

Sample results