DM禁用约束和索引
程序员文章站
2022-06-03 09:45:21
...
工作中,迁移的时候往往需要需要先禁用约束和索引,下面介绍如何禁用所有约束和索引:
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';