DM禁用约束和索引

工作中，迁移的时候往往需要需要先禁用约束和索引，下面介绍如何禁用所有约束和索引：

declare
		var1     varchar(20) := 'SCHEMA_NAME';
        con_name varchar(100);
        tab_name varchar(100);
        idx_name varchar(100);
        sql1     varchar(500);
        sql2     varchar(500);
        sql3     varchar(500);
        sql4     varchar(500);
        c1 cursor;
        c2 cursor;
        begin
               --禁用所有约束
                sql1 = 'SELECT TABLE_NAME, CONSTRAINT_NAME FROM  DBA_CONSTRAINTS WHERE OWNER = '''||var1||''';';
                open c1 for sql1;
                        LOOP
                                fetch c1 into tab_name, con_name;
                        EXIT
                WHEN c1%NOTFOUND;
                        sql2 = 'ALTER TABLE ' || var1 || '.' ||tab_name || ' DISABLE CONSTRAINT "' || con_name || '";' ;
                        execute immediate sql2;
                end loop;
                close c1;
              --失效所有索引
               sql3 = 'SELECT INDEX_NAME FROM DBA_INDEXES WHERE INDEX_TYPE=''NORMAL'' AND OWNER = '''||var1||''';';
               open c2 for sql3;
               		   LOOP
               		   		     fetch c2 into idx_name;
                       EXIT
               WHEN c2%NOTFOUND;
                        sql4 = 'ALTER INDEX ' || var1 || '.' ||idx_name || ' UNUSABLE' ;
                        execute immediate sql4;
               end loop;
               close c2;
               		   			
        end

上述方式分区表不适用

禁用外键约束

--DISABLE CONSTRAINT <约束名> [RESTRICT | CASCADE] --

select 'alter table '||OWNER||'.'||TABLE_NAME||' DISABLE CONSTRAINT '||constraint_name||';' 
from dba_constraints where owner='模式名' and constraint_type='R';

启动外键约束

select 'alter table '||OWNER||'.'||TABLE_NAME||' ENABLE CONSTRAINT '||constraint_name||';' 
from dba_constraints where owner='模式名' and constraint_type='R';