How to Disable All Constraints in Oracle

Hi Guy's,

Today we will see how to disable all the constraints in oracle table.


SQL> select table_name,constraint_name, status from user_constraints where table_name='SHSHTDTA'

TABLE_NAME   CONSTRAINT_NAME       STATUS
--------------- --------------- --------------------------------------------
SHSHTDTA          SYS_C0053639              ENABLED
SHSHTDTA          SYS_C0053640              ENABLED
SHSHTDTA          SYS_C0053641              ENABLED
SHSHTDTA          SYS_C0053642              ENABLED
SHSHTDTA          SYS_C0053643              ENABLED
SHSHTDTA          SYS_C0053644              ENABLED
SHSHTDTA          SYS_C0053645              ENABLED
SHSHTDTA          SYS_C0053646              ENABLED

8 rows selected.



SQL>   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' AND  t.table_name='EMP' 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;
    /

PL/SQL procedure successfully completed.




SQL> select constraint_name,status from dba_constraints where table_name='SHSHTDTA';

CONSTRAINT_NAME          STATUS
--------------- --------------------------------------
SYS_C0041058                    DISABLED
SYS_C0041057                    DISABLED
SYS_C0041056                    DISABLED
SYS_C0041055                    DISABLED
SYS_C0041054                    DISABLED
SYS_C0041053                    DISABLED
SYS_C0041052                    DISABLED
SYS_C0041051                    DISABLED
SHSHTDTA_TMP_PK        DISABLED

9 rows selected.



Happy Reading...