parition index disable

 

-- CLEAN & SIMPLE – RUN THIS IN THE TARGET DATABASE

SELECT 'FACT_SALES'           AS table_name, COUNT(*) AS rows_june_2025 FROM FACT_SALES           PARTITION (ARCHV_DT_202506) UNION ALL

SELECT 'FACT_PURCHASES'      ,                COUNT(*)        FROM FACT_PURCHASES      PARTITION (ARCHV_DT_202506) UNION ALL

SELECT 'FACT_INVENTORY'      ,                COUNT(*)        FROM FACT_INVENTORY      PARTITION (ARCHV_DT_202506) UNION ALL

SELECT 'FACT_SHIPMENTS'      ,                COUNT(*)        FROM FACT_SHIPMENTS      PARTITION (ARCHV_DT_202506) UNION ALL

SELECT 'FACT_RETURNS'        ,                COUNT(*)        FROM FACT_RETURNS        PARTITION (ARCHV_DT_202506) UNION ALL

SELECT 'FACT_CLAIMS'         ,                COUNT(*)        FROM FACT_CLAIMS         PARTITION (ARCHV_DT_202506) UNION ALL

SELECT 'FACT_ADJUSTMENTS'    ,                COUNT(*)        FROM FACT_ADJUSTMENTS    PARTITION (ARCHV_DT_202506)

ORDER BY table_name;

===

SET SERVEROUTPUT ON SIZE UNLIMITED

DECLARE

   v_table_owner  VARCHAR2(128) := 'YOUR_SCHEMA_NAME';   -- ← CHANGE THIS

   v_table_name   VARCHAR2(128) := 'YOUR_TABLE_NAME';    -- ← CHANGE THIS

   v_sql          VARCHAR2(4000);

   v_count        INTEGER := 0;

BEGIN

   DBMS_OUTPUT.PUT_LINE('=== STARTING DISABLE OF FOREIGN KEYS ===');

   DBMS_OUTPUT.PUT_LINE('Target table: ' || v_table_owner || '.' || v_table_name);

   DBMS_OUTPUT.PUT_LINE('');

   FOR rec IN (

       SELECT owner, table_name, constraint_name

       FROM dba_constraints

       WHERE constraint_type = 'R'

         AND ( (r_owner, r_constraint_name) IN   -- FKs that point TO our table (child tables)

               (SELECT owner, constraint_name

                FROM dba_constraints

                WHERE owner = v_table_owner

                  AND table_name = v_table_name

                  AND constraint_type IN ('P','U')

               )

            OR                                 -- FKs that our table points FROM (parent references)

               (owner = v_table_owner AND table_name = v_table_name)

             )

         AND delete_rule = 'NO ACTION'   -- optional filter, remove if you want ALL

   )

   LOOP

      BEGIN

         v_sql := 'ALTER TABLE '||rec.owner||'.'||rec.table_name||

                  ' DISABLE CONSTRAINT '||rec.constraint_name;

         EXECUTE IMMEDIATE v_sql;

         DBMS_OUTPUT.PUT_LINE('DISABLED  → '||rec.owner||'.'||rec.table_name||'.'||rec.constraint_name);

         v_count := v_count + 1;

      EXCEPTION

         WHEN OTHERS THEN

            DBMS_OUTPUT.PUT_LINE('FAILED to disable → '||

                                 rec.owner||'.'||rec.table_name||'.'||rec.constraint_name||

                                 ' | Error: '||SQLERRM);

      END;

   END LOOP;

   DBMS_OUTPUT.PUT_LINE('');

   DBMS_OUTPUT.PUT_LINE('=== DISABLE PHASE FINISHED – '||v_count||' constraints disabled ===');

   DBMS_OUTPUT.PUT_LINE('You can now safely run the TOAD copy with:');

   DBMS_OUTPUT.PUT_LINE('PARTITION (ARCHV_DT_202506) AND ROWNUM <= 500');

END;

/

enable

SET SERVEROUTPUT ON SIZE UNLIMITED

DECLARE

   v_table_owner  VARCHAR2(128) := 'YOUR_SCHEMA_NAME';   -- ← SAME AS ABOVE

   v_table_name   VARCHAR2(128) := 'YOUR_TABLE_NAME';    -- ← SAME AS ABOVE

   v_sql          VARCHAR2(4000);

   v_count        INTEGER := 0;

BEGIN

   DBMS_OUTPUT.PUT_LINE('=== STARTING ENABLE NOVALIDATE OF FOREIGN KEYS ===');

   FOR rec IN (

       SELECT owner, table_name, constraint_name

       FROM dba_constraints

       WHERE constraint_type = 'R'

         AND ( (r_owner, r_constraint_name) IN

               (SELECT owner, constraint_name

                FROM dba_constraints

                WHERE owner = v_table_owner

                  AND table_name = v_table_name

                  AND constraint_type IN ('P','U')

               )

            OR (owner = v_table_owner AND table_name = v_table_name)

             )

   )

   LOOP

      BEGIN

         v_sql := 'ALTER TABLE '||rec.owner||'.'||rec.table_name||

                  ' ENABLE NOVALIDATE CONSTRAINT '||rec.constraint_name;

         EXECUTE IMMEDIATE v_sql;

         DBMS_OUTPUT.PUT_LINE('ENABLED NOVALIDATE → '||rec.owner||'.'||rec.table_name||'.'||rec.constraint_name);

         v_count := v_count + 1;

      EXCEPTION

         WHEN OTHERS THEN

            DBMS_OUTPUT.PUT_LINE('FAILED to enable → '||

                                 rec.owner||'.'||rec.table_name||'.'||rec.constraint_name||

                                 ' | Error: '||SQLERRM);

      END;

   END LOOP;

   DBMS_OUTPUT.PUT_LINE('');

   DBMS_OUTPUT.PUT_LINE('=== ALL DONE – '||v_count||' constraints re-enabled with NOVALIDATE ===');

END;

/

======

Step 1 – Disable all foreign keys that can block your insert (run in the TARGET database)

SQL

-- Disable every FK that references your table OR that your table references
BEGIN
   FOR rec IN (
       SELECT owner, table_name, constraint_name
       FROM dba_constraints
       WHERE constraint_type = 'R'
         AND (r_owner, r_constraint_name) IN (
               SELECT owner, constraint_name
               FROM dba_constraints
               WHERE owner = 'YOUR_TARGET_SCHEMA'     -- ← change to your schema
                 AND table_name = 'YOUR_TABLE_NAME'   -- ← change to your actual table name
                 AND constraint_type IN ('P','U')
             )
          OR (owner = 'YOUR_TARGET_SCHEMA' 
              AND table_name = 'YOUR_TABLE_NAME')
   ) LOOP
       EXECUTE IMMEDIATE 
           'ALTER TABLE '||rec.owner||'.'||rec.table_name||
           ' DISABLE CONSTRAINT '||rec.constraint_name;
   END LOOP;
END;
/

Step 2 – Now run the copy in TOAD with this WHERE clause (100% works)

SQL

PARTITION (ARCHV_DT_202506) AND ROWNUM <= 500

Step 3 – After the copy finishes successfully, re-enable all the FKs (with NOVALIDATE so it doesn’t scan millions of rows)

SQL

BEGIN
   FOR rec IN (
       SELECT owner, table_name, constraint_name
       FROM dba_constraints
       WHERE constraint_type = 'R'
         AND (r_owner, r_constraint_name) IN (
               SELECT owner, constraint_name
               FROM dba_constraints
               WHERE owner = 'YOUR_TARGET_SCHEMA'
                 AND table_name = 'YOUR_TABLE_NAME'
                 AND constraint_type IN ('P','U')
             )
          OR (owner = 'YOUR_TARGET_SCHEMA' 
              AND table_name = 'YOUR_TABLE_NAME')
   ) LOOP
       EXECUTE IMMEDIATE 
           'ALTER TABLE '||rec.owner||'.'||rec.table_name||
           ' ENABLE NOVALIDATE CONSTRAINT '||rec.constraint_name;
   END LOOP;
END;
/

Just replace these two placeholders in both scripts: