Disable Constraints

#!/bin/bash
while read line
do /opt/oracle/bin/sqlplus -s <a href="mailto:user@db/password">user@db/password</a> @query.sql $line
done < /tmp/textfile.txt
BEGIN
   FOR c IN   (SELECT c.owner,
                      c.table_name,
                      c.constraint_name
                 FROM user_constraints c,
                      user_tables t
                WHERE c.table_name = t.table_name AND
                      c.status = 'ENABLED'
                ORDER BY c.constraint_type DESC)
   LOOP
      dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' disable constraint ' || c.constraint_name);
   END LOOP;
END;
/
BEGIN
   FOR c IN   (SELECT c.owner, c.table_name, c.constraint_name
              FROM user_constraints c, user_tables t
             WHERE c.table_name = t.table_name AND
                   c.status = 'DISABLED'
             ORDER BY c.constraint_type)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' enable constraint ' || c.constraint_name);
END LOOP;
END; /

Leave a Reply

Your email address will not be published. Required fields are marked *