Skip to content

Instantly share code, notes, and snippets.

@jzi96
Created May 23, 2012 06:55
Show Gist options
  • Save jzi96/2773621 to your computer and use it in GitHub Desktop.
Save jzi96/2773621 to your computer and use it in GitHub Desktop.
Oracle disable all constraints in a schema
SET SERVEROUTPUT ON
exec dbms_output.enable(1000000);
DECLARE
v_typ VARCHAR2(32);
v_name VARCHAR2(32);
v_constraint VARCHAR2(32);
v_sql VARCHAR2(100);
CURSOR c_constraints IS
SELECT table_name, constraint_name
FROM user_constraints
--WHERE constraint_type = 'R'
;
BEGIN
OPEN c_constraints;
LOOP
BEGIN
FETCH c_constraints
INTO v_name, v_constraint;
EXIT WHEN c_constraints%NOTFOUND;
v_sql := 'alter table ' || v_name || ' DISABLE constraint ' || v_constraint;
DBMS_OUTPUT.put_line(v_sql);
EXECUTE IMMEDIATE v_sql;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error in exec ' || v_sql);
END;
END LOOP;
CLOSE c_constraints;
v_sql := 'PURGE RECYCLEBIN';
DBMS_OUTPUT.put_line(v_sql);
EXECUTE IMMEDIATE v_sql;
END;
/
EXIT;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment