ORACLE -- drop ALL table constraints

finance_teacher
finance_teacher used Ask the Experts™
on
What can I run to DISABLE all 15 of
my GML_P_COMPONENTS table constraints ?

--below works on one constraint
alter table
   GML_P_COMPONENTS
DISABLE constraint
   SYS_C007539;

--below works on ALL constraints ??
alter table
   GML_P_COMPONENTS
DISABLE ALL constraint;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Oracle Database Administrator III
Commented:
From oracle.toolbox.com:
set feedback off
set verify off
set echo off
prompt Finding constraints to disable...
set termout off
set pages 80
set heading off
set linesize 120
spool tmp_disable.sql
select 'spool igen_disable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||
' DISABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c, user_tables u
where c.table_name = u.table_name;
select 'exit;' from dual;
set termout on
prompt Disabling constraints now...
set termout off
@tmp_disable.sql;
exit
/
 
TolomirAdministrator
Top Expert 2005

Commented:
just a remark, this works just with the sql-plus tool, not in a standard sql interpreter.
David VanZandtOracle Database Administrator III

Commented:
As I recall the SET commands do not work in the GUI tool, I would try removing them if I had too -- but building a dynamic SQL script from the command line SQL is less likely to have a "gotcha".
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Hi,
If you don't want to build a script from a script, you can do a pl/sql procedure that reads user_constraints and uses 'execute immediate' to run DDL.
Regards,
Franck.
Naveen KumarProduction Manager / Application Support Manager

Commented:
alter table xyz disable all triggers ; --> this works for all triggers

but a similar thing for disabling all constraints on a given table is not there
to do it with a single command.

You need to do it with multiple commands only. one command to disable each constraint. you can prepare the script manually or spool it with a select query.
awking00Information Technology Specialist
Commented:
Per franckpachot's suggestion, see attached.
procedure.txt
David VanZandtOracle Database Administrator III

Commented:
In both the PL/SQL and my dynamic alternatives, we do not give the asker the SET formatting desired.
awking00Information Technology Specialist

Commented:
dvz,
I'm not sure I understand your concern for the SET formatting. It seems to me that the question was, "What can I run to DISABLE all 15 of my GML_P_COMPONENTS table constraints?" and I submit that the answer is to run the procedure I provided.

Author

Commented:
awking00, the below works good on one table.
How can I loop to get all tables in the "HR" schema ?
--------------------------------------------------------------------
declare
v_sql varchar2(255);
begin
for c in
(select constraint_name
 from user_constraints
 where table_name = 'GML_ITEMMSTR')
loop
v_sql := 'alter table gml_ITEMMSTR disable constraint '||c.constraint_name;
execute immediate v_sql;
end loop;
end;
awking00Information Technology Specialist

Commented:
See attached.
procedure.txt

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial