Simon Krenger

During maintenance, I had to disable all constraints of a table. I knew that Oracle SQL Developer (I really like it even though it is a developer tool and not aimed at Database Administrators) had built-in functions to do this, but since I could only access the database machine via SSH, I had to do it in SQL*Plus.

Luckily, SQL Developer is quite transparent about the commands it uses and I could therefore easily see what is going on when you disable all constraints on a table using the GUI. So here it goes…

Disable all constraints of a table:

begin
	for cur in (select owner, constraint_name , table_name 
		from all_constraints
		where owner = 'SIMON' and
		TABLE_NAME = 'MY_TABLE') loop
	  execute immediate 'ALTER TABLE '||cur.owner||'.'||cur.table_name||' 
	  MODIFY CONSTRAINT "'||cur.constraint_name||'" DISABLE ';
   end loop;
end;
/

Remember to enable the constraints again before ending maintenance or else you might end up with a logically inconsistent database:

begin
	for cur in (select owner, constraint_name , table_name 
		from all_constraints
		where owner = 'SIMON' and
		TABLE_NAME = 'MY_TABLE') loop
	  execute immediate 'ALTER TABLE '||cur.owner||'.'||cur.table_name||'
	  MODIFY CONSTRAINT "'||cur.constraint_name||'" ENABLE ';
   end loop;
end;