Disable all table Constraints Oracle

Below I have attached a script I use to disable all scripts in the specified table, which works perfect. I am unsure on how I would modify this so it would disable all constraints in the entire database.

-- DISABLE --
accept tab prompt "Enter the name of the table to disable its constraints: "
set serveroutput on
declare
    sql_stm    varchar2(2000);
begin
    dbms_output.enable(1000000);
    for x in (select constraint_name
                    ,decode(constraint_type,'P',' cascade') casc
               from user_constraints
               where table_name = upper('&tab')) loop
        sql_stm := 'alter table &tab disable constraint '||
            x.constraint_name||x.casc;
        dbms_output.put_line(sql_stm);
        execute immediate sql_stm;
    end loop;
end;
/

Open in new window

LVL 2
CurtinPropAsked:
Who is Participating?
 
fluglashConnect With a Mentor Commented:
add outer loop like that:


for y in (select table_name 
             from user_tables) loop
    for x in (select constraint_name
              ,decode(constraint_type,'P',' cascade') casc
              from user_constraints
              where table_name = y.table_name) loop
        sql_stm := 'alter table ' || y.table_name || ' disable constraint '||
            x.constraint_name||x.casc;
        dbms_output.put_line(sql_stm);
        execute immediate sql_stm;
    end loop;
end loop;

Open in new window

0
 
SujithConnect With a Mentor Data ArchitectCommented:
Disabling all the constraints in the database could break a lot and lot of things.
Are you sure you want to do that?

See the changed script. It disables all the constraints of the current database schema.
You can change user_constraints to dba_constraints for all the constraints in the database. But check all the constraints to make sure that they are supposed to be dropped.
set serveroutput on
declare
    sql_stm    varchar2(2000);
begin
    dbms_output.enable(1000000);
    for x in (select table_name, constraint_name
                    ,decode(constraint_type,'P',' cascade') casc
               from user_constraints ) loop
        sql_stm := 'alter table '||x.table_name||' disable constraint '||
            x.constraint_name||x.casc;
        dbms_output.put_line(sql_stm);
        execute immediate sql_stm;
    end loop;
end;
/

Open in new window

0
 
Ivo StoykovConnect With a Mentor Commented:
Hi

Here is a sample. It will create a script you could run in sqlplus or Command window

HTH

I
set linesize 120;
SET head off;
SET echo off;
SET pagesize 0;
SET feedback off;
 
SPOOL disable_all_constraints.sql;
SELECT 'ALTER TABLE ' ||
       owner ||
       '.' ||
       table_name ||
       ' DISABLE CONSTRAINT ' ||
       constraint_name ||
       ';' "ALTER Statement"
  FROM dba_constraints
/
 
SPOOL off;

Open in new window

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
If you are going to attempt all tables in the database, it would be much safer to specify a list of owners.  At a minimum, you should exclude SYS and SYSTEM.  Disabling constraints in those schemas would most likely render your database unsupportable.
0
 
SujithData ArchitectCommented:
@johnsone:
Very good point.
0
 
dbmullenConnect With a Mentor Commented:
you provided your own answer
just remove the accept and the "where clause"
-- DISABLE --
--accept tab prompt "Enter the name of the table to disable its constraints: "
set serveroutput on
declare
    sql_stm    varchar2(2000);
begin
    dbms_output.enable(1000000);
    for x in (select constraint_name
                    ,decode(constraint_type,'P',' cascade') casc
               from user_constraints
--               where table_name = upper('&tab')
               ) loop
        sql_stm := 'alter table &tab disable constraint '||
            x.constraint_name||x.casc;
        dbms_output.put_line(sql_stm);
        execute immediate sql_stm;
    end loop;
end;
/

Open in new window

0
 
sakthikumarCommented:
Is there any single alter option to disable all constraints that belong to a table
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.