SELECT
owner,
table_name,
constraint_type,
status,
validated,
COUNT(*) AS constraint_count
FROM
all_constraints
WHERE
owner = 'YOUR_SCHEMA_NAME' -- <<<<< IMPORTANT: Replace with the actual schema owner
AND table_name IN ( -- <<<<< IMPORTANT: List your 40-50 table names here
'TABLE_NAME_1',
'TABLE_NAME_2',
'TABLE_NAME_3',
-- ... up to 40-50 table names
'TABLE_NAME_N'
)
GROUP BY
owner,
table_name,
constraint_type,
status,
validated
ORDER BY
owner,
table_name,
constraint_type, validated DESC; -- Show NOT VALIDATED first for each group
-- Run this SQL command ONCE in your schema (e.g., MY_SCHEMA)
CREATE OR REPLACE TYPE T_VARCHAR2_LIST IS VARRAY(4000) OF VARCHAR2(128);
/
SET SERVEROUTPUT ON SIZE UNLIMITED; -- Ensure output is not truncated and displayed in your SQL client
DECLARE
-- Configuration Parameters (IMPORTANT: Customize these)
p_schema_name VARCHAR2(128) := 'YOUR_SCHEMA_NAME'; -- <<<<< Set your target schema here
-- <<<<< List your target table names here (up to 40-50)
-- Use the SQL type T_VARCHAR2_LIST defined above
p_table_names T_VARCHAR2_LIST := T_VARCHAR2_LIST(
'TABLE_NAME_1',
'TABLE_NAME_2',
'TABLE_NAME_3',
-- Add all your table names here, separated by commas
'TABLE_NAME_N'
);
-- Internal Variables
v_sql_stmt VARCHAR2(1000);
v_constraint_count NUMBER := 0;
v_separator VARCHAR2(80) := RPAD('-', 80, '-'); -- For visual separation
v_header_format VARCHAR2(200); -- For formatted output header
v_detail_format VARCHAR2(200); -- For formatted output details
BEGIN
-- Initialize format strings for visual output
v_header_format := RPAD('OWNER', 15) || RPAD('TABLE_NAME', 25) || RPAD('CONSTRAINT_NAME', 30) || RPAD('TYPE', 8) || RPAD('STATUS', 8) || 'VALIDATED';
v_detail_format := RPAD('%s', 15) || RPAD('%s', 25) || RPAD('%s', 30) || RPAD('%s', 8) || RPAD('%s', 8) || '%s';
-- --- REPORTING PHASE ---
DBMS_OUTPUT.PUT_LINE(v_separator);
DBMS_OUTPUT.PUT_LINE('-- Constraint Analysis Report for Schema: ' || p_schema_name);
DBMS_OUTPUT.PUT_LINE('-- Tables being analyzed: ' || p_table_names.COUNT || ' tables.');
DBMS_OUTPUT.PUT_LINE(v_separator);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('Finding constraints that are:');
DBMS_OUTPUT.PUT_LINE(' - Enabled');
DBMS_OUTPUT.PUT_LINE(' - VALIDATED (target for conversion to NOVALIDATE)');
DBMS_OUTPUT.PUT_LINE(' - Of type CHECK (C), PRIMARY KEY (P), or UNIQUE (U)');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(v_separator);
DBMS_OUTPUT.PUT_LINE('Here are the constraints found matching the criteria:');
DBMS_OUTPUT.PUT_LINE(v_separator);
DBMS_OUTPUT.PUT_LINE(v_header_format);
DBMS_OUTPUT.PUT_LINE(v_separator);
-- Corrected SQL query using TABLE() to convert PL/SQL collection to SQL collection
FOR con_rec IN (
SELECT /*+ NO_PARALLEL */
ac.owner,
ac.table_name,
ac.constraint_name,
ac.constraint_type,
ac.status,
ac.validated,
ac.search_condition_vc
FROM
all_constraints ac
WHERE
ac.owner = p_schema_name
AND ac.table_name IN (SELECT COLUMN_VALUE FROM TABLE(p_table_names)) -- Corrected line
AND ac.constraint_type IN ('C', 'P', 'U')
AND ac.status = 'ENABLED'
AND ac.validated = 'VALIDATED'
ORDER BY
ac.owner,
ac.table_name,
ac.constraint_type,
CASE WHEN ac.validated = 'NOT VALIDATED' THEN 1 ELSE 2 END,
ac.constraint_name
) LOOP
v_constraint_count := v_constraint_count + 1;
-- Output constraint details in a formatted way
DBMS_OUTPUT.PUT_LINE(
UTL_LMS.FORMAT_MESSAGE(v_detail_format,
con_rec.owner,
con_rec.table_name,
con_rec.constraint_name,
con_rec.constraint_type,
con_rec.status,
con_rec.validated)
);
-- Print search_condition_vc if available (for CHECK constraints)
IF con_rec.constraint_type = 'C' AND con_rec.search_condition_vc IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE(RPAD(' ', 15) || 'Condition: ' || con_rec.search_condition_vc);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(v_separator);
DBMS_OUTPUT.PUT_LINE('Total constraints found matching criteria: ' || v_constraint_count);
DBMS_OUTPUT.PUT_LINE(v_separator);
-- --- SQL GENERATION PHASE ---
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(v_separator);
DBMS_OUTPUT.PUT_LINE('-- Generated SQL to set identified constraints to ENABLE NOVALIDATE --');
DBMS_OUTPUT.PUT_LINE('-- IMPORTANT: REVIEW THESE COMMANDS CAREFULLY BEFORE UNCOMMENTING AND EXECUTING!');
DBMS_OUTPUT.PUT_LINE(v_separator);
IF v_constraint_count > 0 THEN
-- Re-querying using the corrected method to ensure consistency
FOR con_rec IN (
SELECT
ac.owner,
ac.table_name,
ac.constraint_name,
ac.search_condition_vc
FROM
all_constraints ac
WHERE
ac.owner = p_schema_name
AND ac.table_name IN (SELECT COLUMN_VALUE FROM TABLE(p_table_names)) -- Corrected line
AND ac.constraint_type IN ('C', 'P', 'U')
AND ac.status = 'ENABLED'
AND ac.validated = 'VALIDATED'
ORDER BY
ac.owner, ac.table_name, ac.constraint_name
) LOOP
v_sql_stmt := 'ALTER TABLE ' || con_rec.owner || '.' || con_rec.table_name ||
' ENABLE CONSTRAINT ' || con_rec.constraint_name || ' NOVALIDATE;';
DBMS_OUTPUT.PUT_LINE(v_sql_stmt);
-- UNCOMMENT THE LINE BELOW AND THE COMMIT AT THE END TO EXECUTE THE STATEMENTS DIRECTLY.
-- EXECUTE IMMEDIATE v_sql_stmt;
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('-- No constraints found matching the criteria. No SQL generated.');
END IF;
DBMS_OUTPUT.PUT_LINE(v_separator);
DBMS_OUTPUT.PUT_LINE('-- End of generated SQL. ');
-- COMMIT; -- UNCOMMENT THIS IF YOU UNCOMMENTED EXECUTE IMMEDIATE ABOVE
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(v_separator);
DBMS_OUTPUT.PUT_LINE('!!! An ERROR occurred during script execution !!!');
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE(v_separator);
RAISE; -- Re-raise the exception to stop execution and indicate failure
END;
/
No comments:
Post a Comment