constraint check - 3

 

Step-by-Step Implementation

Phase 1: On the PROD Database

1. Create a SQL Collection Type (if you haven't already): This type is needed for the PL/SQL block to handle lists of table names efficiently.

SQL

CREATE OR REPLACE TYPE T_VARCHAR2_LIST IS VARRAY(4000) OF VARCHAR2(128);
/

2. Create a Global Temporary Table to store PROD's constraint information: This will hold the exact state of constraints from your PROD environment.

SQL

CREATE GLOBAL TEMPORARY TABLE temp_prod_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), -- Crucial for matching system-generated names
    status              VARCHAR2(8)   NOT NULL,
    validated           VARCHAR2(13)  NOT NULL
) ON COMMIT PRESERVE ROWS;

3. Insert PROD's constraint data into the temporary table: This captures the "source of truth" for your comparison. You'll insert data for the specific schema and tables you're interested in.

SQL

DECLARE
    p_schema_name      VARCHAR2(128) := 'YOUR_PROD_SCHEMA_NAME'; -- <<<<< Set your PROD schema name
    -- <<<<< List your specific table names from PROD here
    p_table_names      T_VARCHAR2_LIST := T_VARCHAR2_LIST(
        'PROD_TABLE_1',
        'PROD_TABLE_2',
        -- Add all your relevant PROD table names here
        'PROD_TABLE_N'
    );
BEGIN
    INSERT INTO temp_prod_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, -- Get the condition for matching
        status,
        validated
    FROM
        all_constraints
    WHERE
        owner = p_schema_name
        AND table_name IN (SELECT COLUMN_VALUE FROM TABLE(p_table_names))
        AND constraint_type IN ('C', 'P', 'U') -- Only CHECK, PK, UK (adjust if you need FK)
        AND status = 'ENABLED'; -- Only consider enabled constraints for this comparison
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Inserted ' || SQL%ROWCOUNT || ' rows into temp_prod_constraints from PROD.');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error inserting into temp_prod_constraints: ' || SQLERRM);
        RAISE;
END;
/

4. Export data from temp_prod_constraints: Use Toad's Export Wizard for this:

• Right-click on temp_prod_constraints in the Schema Browser -> Export Data -> Export Wizard.

• Choose Delimited Text File (CSV is highly recommended).

• Ensure "Include column headers" is checked.

• Save it as a file (e.g., prod_constraints_export.csv) to your local machine.

---

Phase 2: On the PROD_COPY Database

1. Create the SQL Collection Type (if not already present):

SQL

CREATE OR REPLACE TYPE T_VARCHAR2_LIST IS VARRAY(4000) OF VARCHAR2(128);
/

2. Create an identical Global Temporary Table for importing PROD's data: It must have the exact same structure as temp_prod_constraints.

SQL

CREATE GLOBAL TEMPORARY TABLE temp_prod_constraints ( -- Yes, use the same name for simplicity
    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;

3. Import prod_constraints_export.csv into temp_prod_constraints on PROD_COPY: Use Toad's Import Wizard:

• Connect to PROD_COPY.

• Right-click on temp_prod_constraints in the Schema Browser -> Import Data -> Import Wizard.

• Select "Add File" and choose prod_constraints_export.csv.

• Follow the wizard, ensuring correct delimiter, text qualifier, and header row.

• Choose "Insert" mode.

4. Perform the Comparison Query: Now you can compare PROD_COPY's actual constraints (ALL_CONSTRAINTS) against the PROD data you just imported (temp_prod_constraints). This query will highlight the differences you're interested in.

SQL

SELECT
    'Difference Type' AS difference_category,
    COALESCE(prod_c.table_owner, copy_c.owner) AS owner,
    COALESCE(prod_c.table_name, copy_c.table_name) AS table_name,
    COALESCE(prod_c.constraint_type, copy_c.constraint_type) AS constraint_type,
    COALESCE(prod_c.search_condition_vc, copy_c.search_condition_vc) AS search_condition,
    prod_c.validated AS prod_validated_status,
    copy_c.validated AS copy_validated_status,
    prod_c.status AS prod_status,
    copy_c.status AS copy_status,
    prod_c.constraint_name AS prod_constraint_name,
    copy_c.constraint_name AS copy_constraint_name
FROM
    temp_prod_constraints prod_c
FULL OUTER JOIN
    all_constraints copy_c
    ON prod_c.table_owner = copy_c.owner
    AND prod_c.table_name = copy_c.table_name
    AND prod_c.constraint_type = copy_c.constraint_type
    AND prod_c.search_condition_vc = copy_c.search_condition_vc -- Match by condition text
WHERE
    -- Only show differences in VALIDATED or STATUS, or where a constraint exists in one but not the other
    (
        prod_c.validated IS DISTINCT FROM copy_c.validated OR
        prod_c.status IS DISTINCT FROM copy_c.status OR
        (prod_c.constraint_name IS NULL AND copy_c.constraint_name IS NOT NULL) OR -- Exists in COPY but not PROD
        (prod_c.constraint_name IS NOT NULL AND copy_c.constraint_name IS NULL)    -- Exists in PROD but not COPY
    )
    AND COALESCE(prod_c.table_owner, copy_c.owner) = 'YOUR_PROD_SCHEMA_NAME' -- <<<<< Replace
    AND COALESCE(prod_c.table_name, copy_c.table_name) IN (
        -- <<<<< List your table names here, they should match the list you used in PROD
        'PROD_TABLE_1',
        'PROD_TABLE_2',
        'PROD_TABLE_N'
    )
    AND COALESCE(prod_c.status, copy_c.status) = 'ENABLED' -- Only enabled constraints for comparison
ORDER BY
    table_name, constraint_type, search_condition;

Understanding the Comparison Query:

• FULL OUTER JOIN: This is key. It ensures you see:

  • Rows where constraints exist in both (prod_c and copy_c are not null).

  • Rows where a constraint exists in PROD (prod_c not null, copy_c null).

  • Rows where a constraint exists in PROD_COPY (copy_c not null, prod_c null).

• COALESCE: Used to pick the non-null value for columns like owner, table_name, etc., ensuring they appear even if one side of the join is null.

• prod_c.search_condition_vc = copy_c.search_condition_vc: This is the critical matching criterion for system-generated constraints. It matches based on the actual logic of the constraint.

• WHERE Clause for Differences:

  • prod_c.validated IS DISTINCT FROM copy_c.validated: Finds where the VALIDATED status is different. This will spot your PROD (VALIDATED) vs PROD_COPY (NOT VALIDATED) scenarios.

  • prod_c.status IS DISTINCT FROM copy_c.status: Finds where the STATUS (ENABLED/DISABLED) is different.

  • The IS NULL checks identify constraints present in one environment but not the other (which could also be a root cause of your count differences).

• Filtering: The WHERE clause also includes filters for your specific schema and table names, and for ENABLED status (as you previously focused on ENABLED constraints).

By following these steps, you'll get a precise report of the differences in constraint status (and even existence) between your PROD and PROD_COPY environments, giving you the information needed for targeted synchronization actions.