Oracle - drop table constraints without dropping tables
I'm doing some bulk migration of a large Oracle database. The first step of this involves renaming a whole load of tables as a preparation for dropping them later (but I need to keep the data in them around for now). Any foreign开发者_开发技巧 key constraints on them need to be dropped - they shouldn't be connected to the rest of the database at all. If I were dropping them now I could CASCADE CONSTRAINTS, but rename simply alters the constraints.
Is there a way I can drop all of the constraints that CASCADE CONSTRAINTS would drop without dropping the table itself?
You can do it with dynamic SQL and the data dictionary:
begin
for r in ( select table_name, constraint_name
from user_constraints
where constraint_type = 'R' )
loop
execute immediate 'alter table '|| r.table_name
||' drop constraint '|| r.constraint_name;
end loop;
end;
If the tables are owned by more than one user you'll need to drive from DBA_CONSTRAINTS and include OWNER in the projection and the executed statement. If you want to touch less than all the tables I'm afraid you'll need to specify the list in the WHERE clause, unless there's some pattern to their names.
You can disable/re-enable constraints without dropping them. Take a look at this article.
精彩评论