I want to disable all the constrains in my database with single query.I don"t want to pass table name,constraint name etc to my query.
Please help any one.
To do this task you need to write a procedure.
FOR i 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'
ORDER BY c.constraint_type DESC)
dbms_utility.exec_ddl_statement('alter table "' || i.owner || '"."' || i.table_name || '" disable constraint ' || i.constraint_name);
Other wise you can use below process it is not a single query but can be useful.
'alter table ' || owner || '.' || table_name || ' disable constraint ' ||
-- or 'drop' if you want to permanently remove
constraint_name || ';' from user_constraints;
It will give some statements.
copy the result and run it.