The following block should provide the commands to disable the foreign keys pointing on a table. It works wonderfully in 11.2.0.4 and 12.1.0.2 but not on 12.2.0.1. Does not trigger an error but also no results. You can test it, just replace ‚MYTABLE‘ with a table that has foreign key pointing on it.

DECLARE
  -- table that has Foreignkey pointing on it
  v_tbl_name VARCHAR2(30) := 'MYTABLE';
CURSOR get_massdata_tableinfo
    IS
      SELECT v_tbl_name table_name FROM dual
       
    ;
    CURSOR get_fks(par_target_table user_tables.table_name%TYPE)
    IS
      WITH
      user_constr AS
      (
        SELECT *
         FROM all_constraints
         WHERE owner = sys_context('USERENV', 'CURRENT_SCHEMA')
      )
      SELECT r.constraint_name,
             r.table_name
       FROM user_constr r,
            user_constr t
       WHERE t.table_name = par_target_table
         AND t.constraint_type = 'P'
         AND t.constraint_name = r.r_constraint_name
    ;
BEGIN
  FOR crec IN get_massdata_tableinfo
    LOOP
      --
      dbms_output.put_line('Table Name ' || crec.table_name);
      -- disable FK´s pointing to table
      FOR rec IN get_fks(crec.table_name) --no rows in 12.2.0.1 (but it works in 11.2.0.4 and 12.1.0.2)
      LOOP
        dbms_output.put_line('ALTER TABLE ' || rec.table_name ||
             ' DISABLE CONSTRAINT ' || rec.constraint_name);
        
      END LOOP;
    END LOOP;
    
END;

If I call the SQL directly, then I get records.

WITH
      user_constr AS
      (
        SELECT *
         FROM all_constraints
         WHERE owner = sys_context('USERENV', 'CURRENT_SCHEMA')
      )
      SELECT r.constraint_name,
             r.table_name
       FROM user_constr r,
            user_constr t
       WHERE t.table_name = 'MY_TABLE'
         AND t.constraint_type = 'P'
         AND t.constraint_name = r.r_constraint_name

Is this a bug ?

It looks like the problem is because of the oracle patch 22485591. It works in a 12.2.0.1 without this patch. In addition, a strange thing: When I add the hint no_merge to the following Statement it works also with the path 22485591.

 CURSOR get_fks(par_target_table user_tables.table_name%TYPE)
    IS
      WITH
      user_constr AS
      (
        SELECT /*+ no_merge */ * -- this is very strange
         FROM all_constraints
         WHERE owner = sys_context('USERENV', 'CURRENT_SCHEMA')
      )
      SELECT r.constraint_name,
             r.table_name
       FROM user_constr r,
            user_constr t
       WHERE t.table_name = par_target_table
         AND t.constraint_type = 'P'
         AND t.constraint_name = r.r_constraint_name
    ;

I posted the problem in freelists.org and I get this response from Jonathan Lewis:

——————————
— „deep dive“
——————————
Okay,
I ran your test and got no output on 12.2.01
Traced it (10046 level 4)
Found that the query against all_constraints went parallel and returned no data, so added a /*+ noparallel */ hint to the query, then (belts and braces) „alter session disable parallel query;“
Ran the test again – got the expected result.
It looks like the parallel execution loses the value of sys_context.
I would check whether 12.1.0.2 and 11.2.0.4 ran the query parallel or whether they ran it serially, and if they’re running t serially check what happens if you force it parallel.

—————–
— After the no_merge comment:
—————–

That (ed. the no_merge) may be luck rather than anything else. I just tried the same thing and still saw the problem (and parallel execution).
In your case it’s possible that the presence of the no_merge hint resulted in Oracle materializing the subquery and maybe that made it run serially – i.e. it was about object_statistics rather than functionality.


P.S.  Looking at the execution plans, my 12.2 (corrected from 12.1) is translating all_constraints to a query involving int$int$DBA_CONSTRAINTS, while the 12.1 query is a „more traditional“ massive join of lots of tables – so the base problem seems to start with the appearance of a CDB-mechanism of all_constraints.  (I’m running 12.2. from a PDB, while the 12.1 is non-PDB database)