Disable All Table Constraints in Oracle

Disable all table constraints in Oracle

It is better to avoid writing out temporary spool files. Use a PL/SQL block. You can run this from SQL*Plus or put this thing into a package or procedure. The join to USER_TABLES is there to avoid view constraints.

It's unlikely that you really want to disable all constraints (including NOT NULL, primary keys, etc). You should think about putting constraint_type in the WHERE clause.

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 NOT (t.iot_type IS NOT NULL AND c.constraint_type = 'P')
   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;
/

Enabling the constraints again is a bit tricker - you need to enable primary key constraints before you can reference them in a foreign key constraint. This can be done using an ORDER BY on constraint_type. 'P' = primary key, 'R' = foreign key.

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;
/

Oracle PL SQL disable all constraints of database tables

Your double quotes are wrong. You are generating SQL statements like this:

alter table "DB_NAME.FOOBAR disable constraint some_constraint;

Which misses the second double quote (my guess is that you probably wanted to put the second quote after the table name which would have been wrong as well).

You need to put each part of the identifier in quotes, not the whole thing:

alter table "DB_NAME"."FOOBAR" disable constraint some_constraint;

I also don't see the necessity to use dbms_sql:

execute immediate 'alter table "DB_NAME"."' || i.OBJECT_NAME || '" DISABLE CONSTRAINT ' || j.CONSTRAINT_NAME;   

To avoid having to repeat the owner, I would actually change the statement to:

execute immediate 'alter table "'||j.owner||'"."' || i.OBJECT_NAME || '" DISABLE CONSTRAINT ' || j.CONSTRAINT_NAME;   

Thus you only need to "hard-code" the owner name once in the procedure.

Disable all related constraints from a table with Oracle PL/SQL

There is nothing intrinsically wrong with the way you're doing this but there are a number of potential issues and some unnecessary code.

• Your first cursor is unnecessary, you're going to loop through ALL_CONSTRAINTS anyway - if you don't enter the loop then the table has no constraints
• You may attempt to disable already disabled constraints - you only need to select those constraints that are currently enabled.
• Your final select to see whether there are any non-disabled constraints will always fail if there is more than one status as you'll get a TOO_MANY_ROWS exception (for returning more than one row). This means that your final ELSE statement will never be entered.
• It's highly unlikely that a constraint will not be disabled without an exception being raised. I wouldn't even bother with the final check.

More generally, there's rarely a point in outputting information to screen. It requires someone to read it. It's normally better if everything just works, or an exception is raised in the event of an error.

I would simplify this massively to something like the following, which simply loops through the non-disabled constraints and disables them.

create or replace procedure disable_all_constraints (
    p_owner in varchar2
  , p_table_name in varchar2
    ) is

begin

   for cur in ( select owner || '.' || table_name as object
                     , constraint_name
                  from all_constraints
                 where owner = p_owner
                   and table_name = p_table_name
                   and status <> 'DISABLED'
                       ) loop

      execute immediate 'alter table ' || cur.object || ' 
                           modify constraint ' || cur.constraint_name || '
                           disable cascade';

   end loop;

end;

If you feel like you have to have the extra checks and printing this can be done more cleanly:

create or replace procedure disable_all_constraints (
    p_owner in varchar2
  , p_table_name in varchar2
    )  is

   l_has_constraint boolean := False;
   l_ct number;

begin

   for cur in ( select owner || '.' || table_name as object
                     , constraint_name
                  from all_constraints
                 where owner = p_owner
                   and table_name = p_table_name
                   and status <> 'DISABLED'
                       ) loop

      l_has_constraint := True;
      execute immediate 'alter table ' || cur.object || ' 
                           modify constraint ' || cur.constraint_name || '
                           disable cascade';

   end loop;

   if not l_has_constraint then
       dbms_output.put_line('No Constraints added to the table.');
   else
       select count(*) into l_ct
         from all_constraints
        where owner = p_owner
          and table_name = p_table_name
          and status <> 'DISABLED'
              ;

       if l_ct = 0 then
          dbms_output.put_line('All related constraints disable successfully');
       else
          dbms_output.put_line('Something went wrong, but that is impossible');
       end if;
    end if;


end;

Oracle SQL Developer - Disabling all constraints from schema

As per my comment above, it isn't sufficient to run the 2 queries, you then need to run all the alter table statements that these have generated. However you could do it all at once using PL/SQL. I have combined the 2 queries into one, using order by to process the foreign keys (constraint_type = 'R') first:

begin
  for r in
    ( select 'alter table '||table_name||' disable constraint '||constraint_name as statement
      from user_constraints
      where status = 'ENABLED'
      order by case constraint_type when 'R' then 1 else 2 end
    )
  loop
    execute immediate r.statement;
  end loop;
end;

Disable all foreign key constraints associated to specific table

Say you have tables like these:

create table MY_TABLE ( CODE number primary key);
create table anotherTable ( code_ref number);
alter table  anotherTable add constraint ck1 foreign key ( code_ref) references my_table ( code);
create table yetAnotherTable ( code_ref number);
alter table  yetAnotherTable add constraint ck2 foreign key ( code_ref) references my_table ( code);

You can use something like the following to loop through all the constraints referring to a given column of a table and disable/enable them with:

begin
    for s in (
                SELECT 'alter table ' || c2.table_name || ' modify constraint ' || c2.constraint_name || ' disable' as statement
                FROM all_constraints c
                       INNER JOIN all_constraints c2
                         ON ( c.constraint_name = c2.r_constraint_name AND c.owner = c2.owner)
                       INNER JOIN all_cons_columns col
                         ON ( c.constraint_name = col.constraint_name AND c.owner = col.owner) 
                WHERE c2.constraint_type = 'R'
                  AND c.table_name = 'MY_TABLE'
                  AND c.owner = 'ALEK'
                  AND col.column_name = 'CODE'
             )
    loop
        dbms_output.put_line(s.statement);
        execute immediate s.statement;
    end loop;
end;

This gives (and executes):

alter table YETANOTHERTABLE modify constraint CK2 disable
alter table ANOTHERTABLE modify constraint CK1 disable

Temporarily Disabling Constraints in Oracle Database

6 minutes to delete a single row does not sound like an issue with constraints on the table. There are three reasonably likely mechanisms that would cause that level of performance problem. Most to least likely in my experience

1. The table is the parent to one or more large child tables. The child tables have an enforced foreign key reference to the parent table but the foreign key column is not indexed in the child table. If this is the case, the issue is that Oracle has to do a full scan of the table every time you delete a row from the parent in order to verify that there are no orphans. You could go to each child table and drop the foreign key but it would almost certainly make more sense to index the foreign key column in the child table(s). It is very rare that you want to have unindexed foreign keys.
2. There is a trigger on the table and the trigger is doing something that takes 6 minutes. You'd have to look at the trigger code to see exactly what was taking so long there.
3. You are doing a single-row delete as a transaction and you have an on commit materialized view that needs to be updated as a result of the change. It's hard to come up with a way to build something like this that would take 6 minutes to run but there are certainly ways to make this slow.

---

Related Topics