I just came across this really nice new feature in 12.2 (and looked back to find it was in 12.1). If you work with the data dictionary and with the *_CONSTRAINTS table, feast your eyes on this description:
SQL> desc dba_constraints
Name Null? Type
------------------------------------------------
OWNER VARCHAR2(128)
CONSTRAINT_NAME NOT NULL VARCHAR2(128)
CONSTRAINT_TYPE VARCHAR2(1)
TABLE_NAME NOT NULL VARCHAR2(128)
SEARCH_CONDITION LONG
SEARCH_CONDITION_VC VARCHAR2(4000) R_OWNER VARCHAR2(128)
R_CONSTRAINT_NAME VARCHAR2(128)
DELETE_RULE VARCHAR2(9)
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATED VARCHAR2(13)
GENERATED VARCHAR2(14)
BAD VARCHAR2(3)
RELY VARCHAR2(4)
LAST_CHANGE DATE
INDEX_OWNER VARCHAR2(128)
INDEX_NAME VARCHAR2(128)
INVALID VARCHAR2(7)
VIEW_RELATED VARCHAR2(14)
ORIGIN_CON_ID NUMBER
Of course, I am sure you noticed the new column - SEARCH_CONDITION_VC! A Search condition we can easily search! No more of this:
select a.owner, a.table_name, a.search_condition, b.column_name, count(*)
from dba_constraints a, dba_cons_columns b
where a.owner=b.owner
and a.table_name=b.table_name
and a.constraint_name=b.constraint_name
and a.search_condition_vc like '%''t'',''f''%'
group by a.owner, a.table_name, a.search_condition, b.column_name;
group by a.owner, a.table_name, a.search_condition, b.column_name
*
ERROR at line 7:
ORA-00997: illegal use of LONG datatype
Now we can do this!!
select a.owner, a.table_name, a.search_condition_vc, b.column_name, count(*)
from dba_constraints a, dba_cons_columns b
where a.owner=b.owner
and a.table_name=b.table_name
and a.constraint_name=b.constraint_name
and a.search_condition_vc like '%''t'',''f''%'
group by a.owner, a.table_name, a.search_condition_vc, b.column_name;
OWNER TABLE_NAME SEARCH_CONDITION_VC COLUMN_NAME COUNT(*)
---------- ------------------------------ ---------------------- ---------------- --
PROD LCONFIG ride IN ('t','f') RIDE 1
PROD VENT pvent in ('t','f','p','s','c') VENT 1
Very nice....
