Найти только определенные пользователем проверочные ограничения в Oracle ALL_CONSTRAINTS

Я, конечно, мог бы сделать эвристику о маловероятности того, что кто-то использует точный синтаксис "COLUMN_NAME" IS NOT NULL (включая двойные кавычки):

SELECT *
FROM all_constraints
WHERE constraint_type = 'C'
AND table_name = 'FOO'
AND search_condition_vc NOT IN (
  SELECT '"' || column_name || '" IS NOT NULL'
  FROM all_tab_cols
  WHERE table_name = 'FOO'
  AND nullable = 'N'
);

Это дает мне желаемый результат:

CONSTRAINT_NAME   SEARCH_CONDITION   GENERATED
---------------------------------------------------
SYS_C00120657     b is not null      GENERATED NAME
SYS_C00120658     c > 1              GENERATED NAME

Я представляю это как ответ, так как это может быть достаточно для некоторых людей, но мне бы очень хотелось более надежное решение.

SYS.CDEF$.TYPE# знает разницу между неявными и явными проверочными ограничениями. Неявные проверочные ограничения сохраняются как 7, явные проверочные ограничения сохраняются как 1.

--Explicit constraints only.
select constraint_name, search_condition
from dba_constraints
where (owner, constraint_name) not in
    (
        --Implicit constraints.
        select dba_users.username, sys.con$.name
        from sys.cdef$
        join sys.con$
            on cdef$.con# = con$.con#
        join dba_users
            on sys.con$.owner# = dba_users.user_id
        where cdef$.type# = 7
    )
    and constraint_type = 'C'
    and table_name = 'FOO'
order by 1;


CONSTRAINT_NAME   SEARCH_CONDITION
---------------   ----------------
SYS_C00106940     b is not null
SYS_C00106941     c > 1

Это решение имеет очевидный недостаток - использование недокументированных таблиц. Но это кажется более точным, чем полагаться на текст условия. Некоторые неявные проверочные ограничения не создаются с двойными кавычками. Я не могу воспроизвести эту проблему, но обнаружил, что это происходит со столом SYS.TAB$.

Идея: Вы можете сравнить таблицу с ее «теневым» аналогом. CREATE TABLE AS не сохраняет определенные пользователем проверочные ограничения:

-- original table
CREATE TABLE foo (
  id int PRIMARY KEY NOT NULL,
  a int not null,              -- Implicit not null constraint
  b int check (b is not null), -- Explicit not null constraint
  c int check (c = 1),          -- Explicit constraint
  d INT CONSTRAINT my_check CHECK (d = 3)
);

-- clone without data(it should be stored in different schema than actual objects)
CREATE TABLE shadow_foo
AS
SELECT * 
FROM foo 
WHERE 1=2;

-- for Oracle 18c you could consider private temporary tables
CREATE PRIVATE TEMPORARY TABLE ora$shadow_foo ON COMMIT DROP DEFINITION
AS
SELECT * FROM foo WHERE 1=2;

И основной запрос:

SELECT c.*
FROM (SELECT * FROM all_constraints WHERE TABLE_NAME NOT LIKE 'SHADOW%') c
LEFT JOIN (SELECT * FROM all_constraints WHERE TABLE_NAME LIKE 'SHADOW%') c2
  ON c2.table_name = 'SHADOW_' || c.table_name
 AND c2.owner = c.owner
 AND c2.search_condition_vc = c.search_condition_vc
WHERE c2.owner IS NULL
  AND c.constraint_type = 'C'
  AND c.owner  LIKE 'FIDDLE%'

db <> fiddle demo