CONSTRAINT CHECK

 

Phase 1: On the Source Database (e.g., Production)

1. Create a Global Temporary Table to store source constraint information. This table will hold the constraint details from your production environment.

SQL

CREATE GLOBAL TEMPORARY TABLE temp_source_constraints (

    table_owner VARCHAR2(128) NOT NULL,

    table_name VARCHAR2(128) NOT NULL,

    constraint_name VARCHAR2(128) NOT NULL,

    constraint_type VARCHAR2(1) NOT NULL,

    search_condition_vc VARCHAR2(4000), -- Stores the actual condition for matching

    status VARCHAR2(8) NOT NULL,

    validated VARCHAR2(13) NOT NULL

) ON COMMIT PRESERVE ROWS;

ON COMMIT PRESERVE ROWS: This ensures the data you insert into this temporary table persists for the duration of your session, even after a COMMIT.

2. Insert relevant constraint data from your source table into the temporary table. We'll focus on CHECKconstraints (CONSTRAINT_TYPE = 'C'). You can also include PRIMARY KEY ('P') and UNIQUE ('U') constraints if you need to synchronize their VALIDATED status as well.

SQL

INSERT INTO temp_source_constraints (

    table_owner,

    table_name,

    constraint_name,

    constraint_type,

    search_condition_vc,

    status,

    validated

)

SELECT

    owner,

    table_name,

    constraint_name,

    constraint_type,

    search_condition_vc, -- This is crucial for matching across databases

    status,

    validated

FROM

    all_constraints

WHERE

    owner = 'YOUR_SOURCE_SCHEMA_NAME' -- <<<<< IMPORTANT: Replace with your actual production schema name

AND table_name = 'YOUR_SOURCE_TABLE_NAME' -- <<<<< IMPORTANT: Replace with your actual production table name

AND constraint_type IN ('C', 'P', 'U'); -- Add 'P', 'U' if you need to sync PK/UK validated status

COMMIT; -- Commit the insert into the GTT (data remains due to ON COMMIT PRESERVE ROWS)

3. Export the data from temp_source_constraints table. Use Toad's Export Wizard for this:

In Toad, open the Schema Browser.Navigate to your user's temporary tables or find temp_source_constraints.Right-click on temp_source_constraints and choose Export Data > Export Wizard.On the "Select Output Format" page, choose Delimited Text File (CSV is generally the most portable and easiest to import).Ensure "Include column headers" is checked.Specify a file name (e.g., source_constraints_sync.csv) and a location on your local machine.Click through the remaining steps and "Export Data Now".

---

Phase 2: On the Target Database (e.g., Development/Test)

1. Create an identical Global Temporary Table on the target database. The structure must be exactly the same as the one on the source.

SQL

CREATE GLOBAL TEMPORARY TABLE temp_target_constraints_sync (

    table_owner VARCHAR2(128) NOT NULL,

    table_name VARCHAR2(128) NOT NULL,

    constraint_name VARCHAR2(128) NOT NULL,

    constraint_type VARCHAR2(1) NOT NULL,

    search_condition_vc VARCHAR2(4000),

    status VARCHAR2(8) NOT NULL,

    validated VARCHAR2(13) NOT NULL

) ON COMMIT PRESERVE ROWS;

2. Import the source_constraints_sync.csv file into temp_target_constraints_sync on the target database.Use Toad's Import Wizard:

In Toad, connect to your target database.Open the Schema Browser.Navigate to your user's temporary tables or find temp_target_constraints_sync.Right-click on temp_target_constraints_sync and choose Import Data > Import Wizard.On the "Import File" page, click "Add File" and browse to select the source_constraints_sync.csv file you exported earlier.Follow the wizard, ensuring correct delimiter, text qualifier, and that "Column name as header" is checked.On the "Select Target" page, ensure "A single existing table" is selected and temp_target_constraints_sync is chosen.Choose "Insert" as the import mode.Click through the remaining steps and "Import Data Now".

3. Generate and Execute Dynamic SQL to Synchronize VALIDATED Status. This is the core synchronization step. This PL/SQL block will generate ALTER TABLE ... ENABLE CONSTRAINT ... NOVALIDATE statements for constraints on your target table that meet these conditions:

They are CHECK, PRIMARY KEY, or UNIQUE constraints.Their SEARCH_CONDITION_VC matches a constraint from the temp_target_constraints_sync table (which holds the source's constraint info).Their current VALIDATED status on the target is 'VALIDATED', but the source constraint (from temp_target_constraints_sync) has a VALIDATED status of 'NOT VALIDATED'.

SQL

SET SERVEROUTPUT ON SIZE UNLIMITED; -- Enable output in your SQL client

DECLARE

    v_sql_stmt VARCHAR2(1000);

-- IMPORTANT: Replace these with your actual target table and schema names

    v_target_table_name VARCHAR2(128) := 'YOUR_TARGET_TABLE_NAME';

    v_target_schema_name VARCHAR2(128) := 'YOUR_TARGET_SCHEMA_NAME';

BEGIN

    DBMS_OUTPUT.PUT_LINE('-- Generating ALTER statements to synchronize VALIDATED status for ' || v_target_schema_name || '.' || v_target_table_name);

    DBMS_OUTPUT.PUT_LINE('-- Review these commands carefully before executing.');

    DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------');

FOR con_rec IN (

SELECT

            ac.owner AS target_owner,

            ac.table_name AS target_table,

            ac.constraint_name AS target_constraint_name,

            ac.validated AS current_target_validated_status,

            tsc.validated AS source_validated_status_to_match,

            tsc.search_condition_vc AS source_constraint_condition

FROM

            all_constraints ac

JOIN

            temp_target_constraints_sync tsc

ON ac.owner = v_target_schema_name -- Match target schema

AND ac.table_name = v_target_table_name -- Match target table

AND ac.constraint_type = tsc.constraint_type -- Match constraint type ('C', 'P', 'U')

AND ac.search_condition_vc = tsc.search_condition_vc -- *** CRITICAL: Match by actual condition ***

WHERE

            ac.owner = v_target_schema_name

AND ac.table_name = v_target_table_name

AND ac.constraint_type IN ('C', 'P', 'U') -- Only CHECK, PK, UK constraints

AND ac.status = 'ENABLED' -- Only consider enabled constraints on target

AND ac.validated = 'VALIDATED' -- Target is currently VALIDATED

AND tsc.validated = 'NOT VALIDATED' -- Source (from GTT) is NOT VALIDATED

    ) LOOP

-- Generate the ALTER statement to set the target constraint to NOVALIDATE

        v_sql_stmt := 'ALTER TABLE ' || con_rec.target_owner || '.' || con_rec.target_table ||

' ENABLE CONSTRAINT ' || con_rec.target_constraint_name || ' NOVALIDATE; -- Matched by condition: ' || con_rec.source_constraint_condition;

        DBMS_OUTPUT.PUT_LINE(v_sql_stmt);

-- UNCOMMENT THE LINE BELOW TO EXECUTE THE STATEMENTS DIRECTLY.

-- EXECUTE IMMEDIATE v_sql_stmt;

END LOOP;

    DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------');

    DBMS_OUTPUT.PUT_LINE('-- Synchronization script generation complete.');

EXCEPTION

WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);

        RAISE; -- Re-raise the exception after printing

END;

/

Final Steps after Running Phase 2, Step 3:

Review the Output: Carefully examine the ALTER TABLE statements printed by DBMS_OUTPUT.Execute Commands: If you didn't uncomment EXECUTE IMMEDIATE, copy the generated ALTER TABLEstatements and run them manually on your target database.Verify: After execution, you can re-query ALL_CONSTRAINTS on your target table to confirm that the VALIDATEDstatus of the relevant constraints now matches the NOT VALIDATED status from your source.

This methodical approach ensures that even with system-generated constraint names, you can accurately identify and synchronize the VALIDATED status based on the actual constraint logic.

SET SERVEROUTPUT ON SIZE UNLIMITED; -- Enable output in your SQL client 

DECLARE 

    v_sql_stmt VARCHAR2(1000); 

    -- IMPORTANT: Replace these with your actual target table and schema names 

    v_target_table_name  VARCHAR2(128) := 'YOUR_TARGET_TABLE_NAME'; 

    v_target_schema_name VARCHAR2(128) := 'YOUR_TARGET_SCHEMA_NAME'; 

BEGIN 

    DBMS_OUTPUT.PUT_LINE('-- Generating ALTER statements to synchronize VALIDATED status for ' || v_target_schema_name || '.' || v_target_table_name); 

    DBMS_OUTPUT.PUT_LINE('-- Review these commands carefully before executing.'); 

    DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------'); 

    FOR con_rec IN ( 

        SELECT 

            ac.owner AS target_owner, 

            ac.table_name AS target_table, 

            ac.constraint_name AS target_constraint_name, 

            ac.validated AS current_target_validated_status, 

            tsc.validated AS source_validated_status_to_match, 

            tsc.search_condition_vc AS source_constraint_condition 

        FROM 

            all_constraints ac 

        JOIN 

            temp_target_constraints_sync tsc 

            ON ac.owner = v_target_schema_name -- Match target schema 

            AND ac.table_name = v_target_table_name -- Match target table 

            AND ac.constraint_type = tsc.constraint_type -- Match constraint type ('C', 'P', 'U') 

            AND ac.search_condition_vc = tsc.search_condition_vc -- *** CRITICAL: Match by actual condition *** 

        WHERE 

            ac.owner = v_target_schema_name 

            AND ac.table_name = v_target_table_name 

            AND ac.constraint_type IN ('C', 'P', 'U') -- Only CHECK, PK, UK constraints 

            AND ac.status = 'ENABLED' -- Only consider enabled constraints on target 

            AND ac.validated = 'VALIDATED' -- Target is currently VALIDATED 

            AND tsc.validated = 'NOT VALIDATED' -- Source (from GTT) is NOT VALIDATED 

    ) LOOP 

        -- Generate the ALTER statement to set the target constraint to NOVALIDATE 

        v_sql_stmt := 'ALTER TABLE ' || con_rec.target_owner || '.' || con_rec.target_table || 

                      ' ENABLE CONSTRAINT ' || con_rec.target_constraint_name || ' NOVALIDATE; -- Matched by condition: ' || con_rec.source_constraint_condition; 

        DBMS_OUTPUT.PUT_LINE(v_sql_stmt); 

        -- UNCOMMENT THE LINE BELOW TO EXECUTE THE STATEMENTS DIRECTLY. 

        -- EXECUTE IMMEDIATE v_sql_stmt; 

    END LOOP; 

    DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------'); 

    DBMS_OUTPUT.PUT_LINE('-- Synchronization script generation complete.'); 

EXCEPTION 

    WHEN OTHERS THEN 

        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM); 

        RAISE; -- Re-raise the exception after printing 

END; 

/ 

what does this script do? 

This PL/SQL script is designed to synchronize the VALIDATED status of certain constraints on a target Oracle table to match a "NOT VALIDATED" state found on a corresponding source table, even when constraint names differ.

Here's a breakdown of what the script does, step by step:Enables Server Output (SET SERVEROUTPUT ON SIZE UNLIMITED;): This command, run before the PL/SQL block, ensures that any messages generated by DBMS_OUTPUT.PUT_LINE within the block are displayed in your SQL client (like SQL*Plus, SQL Developer, Toad, etc.).Declares Variables:v_sql_stmt VARCHAR2(1000): A variable to hold the dynamically generated ALTER TABLE SQL statements.v_target_table_name VARCHAR2(128): Placeholder for the name of the table on the target database whose constraints you want to modify. You must replace 'YOUR_TARGET_TABLE_NAME' with the actual table name.v_target_schema_name VARCHAR2(128): Placeholder for the schema (owner) of the target table. You must replace 'YOUR_TARGET_SCHEMA_NAME' with the actual schema name.Prints Header Information: DBMS_OUTPUT.PUT_LINE statements print comments and a separator to the output, providing context about what the script is doing.Iterates Through Relevant Constraints (FOR con_rec IN (...) LOOP): This is the core logic. The FOR loop iterates over a result set generated by a SELECT statement. This SELECT statement is designed to identify specific constraints that need their VALIDATED status changed:FROM all_constraints ac: Queries the ALL_CONSTRAINTS data dictionary view, which contains information about all constraints accessible to the current user in the current database (this is your targetdatabase).JOIN temp_target_constraints_sync tsc: It joins ALL_CONSTRAINTS (the actual state on target) with temp_target_constraints_sync (which is a global temporary table containing the source's constraint information that you previously imported).ON ac.owner = v_target_schema_name AND ac.table_name = v_target_table_name: These conditions ensure the join focuses on the specific target table you're interested in.AND ac.constraint_type = tsc.constraint_type: This matches constraints by their type (e.g., 'C' for CHECK, 'P' for Primary Key, 'U' for Unique).AND ac.search_condition_vc = tsc.search_condition_vc: This is the MOST CRITICAL part.Since system-generated constraint names (SYS_C...) are inconsistent between databases, this condition matches constraints based on their actual logical definition (e.g., salary > 0 or status IN ('A', 'B')). This allows the script to find corresponding constraints even if their names are different.WHERE ac.owner = v_target_schema_name AND ac.table_name = v_target_table_name: Redundant but harmless, ensures focus.AND ac.constraint_type IN ('C', 'P', 'U'): Filters to include only CHECK, PRIMARY KEY, and UNIQUEconstraints, as these are the ones whose VALIDATED status you typically manage.AND ac.status = 'ENABLED': Only considers constraints on the target table that are currently enabled. You wouldn't be enabling a disabled constraint to NOVALIDATE in this context.AND ac.validated = 'VALIDATED': This filters for constraints on the target table that are currently marked as VALIDATED.AND tsc.validated = 'NOT VALIDATED': This filters for constraints where the source (represented by tsc) has a NOT VALIDATED status.In essence, the loop processes every constraint on the target table that is currently ENABLED VALIDATED but corresponds to a constraint on the source table that was ENABLED NOT VALIDATED.Generates Dynamic ALTER TABLE Statement: Inside the loop, for each con_rec (constraint record) that meets the criteria, the script constructs an ALTER TABLE statement in the v_sql_stmt variable:ALTER TABLE [target_owner].[target_table]ENABLE CONSTRAINT [target_constraint_name]NOVALIDATE;A comment -- Matched by condition: [source_constraint_condition] is appended for clarity, showing the actual condition used for matching.Prints Generated Statement: DBMS_OUTPUT.PUT_LINE(v_sql_stmt); prints the generated ALTER TABLEcommand to the client's output. This allows you to review the commands before executing them.Optional: Executes Statement Directly (EXECUTE IMMEDIATE v_sql_stmt;): This line is commented out (--). If you uncomment it, the script will not just print the commands, but it will also immediately execute each ALTER TABLE statement as it's generated. It is highly recommended to keep this commented out initially and review the generated output before manual execution.Prints Footer Information: After the loop finishes, more DBMS_OUTPUT.PUT_LINE statements confirm the script's completion.Error Handling (EXCEPTION WHEN OTHERS THEN ...): This block catches any unexpected errors that might occur during the script's execution. If an error occurs, it prints the SQL error message (SQLERRM) and then RAISE;re-raises the exception, stopping the PL/SQL block's execution and propagating the error back to the client.In summary, this script is a tool to automate the process of bringing the VALIDATED status of target table constraints in line with those of a source table, specifically converting VALIDATED constraints to NOT VALIDATEDbased on a match of their logical conditions. It's typically used as part of a larger data synchronization or migration process where consistency of constraint validation status is required, especially for ALTER TABLE EXCHANGE PARTITIONoperations.