--禁用指定模式下的约束
create or replace procedure test_varchar_insert
( var1 varchar(100))
as
c_count int := 0;
con_name varchar(100);
tab_name varchar(100);
sql1 varchar(500);
sql2 varchar(500);
c1 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;
end
call test_varchar_insert('DMHR')--其中DMHR为模式名
--1.禁用约束,索引失效
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
--2.启用约束,重建索引
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 || ' ENABLE 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 || ' REBUILD' ;
execute immediate sql4;
end loop;
close c2;
end
--批量生成启用约束禁用约束的SQL语句
--先禁用外键约束
select 'alter table '||OWNER||'.'||TABLE_NAME||' DISABLE CONSTRAINT '||CONSTRAINT_NAME||';',
CONSTRAINT_TYPE,DBA_CONSTRAINTS.STATUS
from DBA_CONSTRAINTS where owner='DMHR' AND CONSTRAINT_TYPE='R';
--再禁用其他约束
select 'alter table '||OWNER||'.'||TABLE_NAME||' DISABLE CONSTRAINT '||CONSTRAINT_NAME||';',
CONSTRAINT_TYPE,DBA_CONSTRAINTS.STATUS
from DBA_CONSTRAINTS where owner='DMHR' AND CONSTRAINT_TYPE IN ('C','P','U');
--先启用其他约束
select 'alter table '||OWNER||'.'||TABLE_NAME||' ENABLE CONSTRAINT '||CONSTRAINT_NAME||';',
CONSTRAINT_TYPE,DBA_CONSTRAINTS.STATUS
from DBA_CONSTRAINTS where owner='DMHR' AND CONSTRAINT_TYPE IN ('C','P','U');
--再启用外键约束
select 'alter table '||OWNER||'.'||TABLE_NAME||' ENABLE CONSTRAINT '||CONSTRAINT_NAME||';',
CONSTRAINT_TYPE,DBA_CONSTRAINTS.STATUS
from DBA_CONSTRAINTS where owner='DMHR' AND CONSTRAINT_TYPE='R';
--查詢所有索引
SELECT INDEX_NAME,STATUS FROM DBA_INDEXES WHERE INDEX_TYPE='NORMAL' AND OWNER = 'DMHR' AND TABLE_NAME NOT LIKE '%_P_%';
--生成禁用normal索引的语句
SELECT 'ALTER INDEX '|| OWNER||'.'||INDEX_NAME ||' UNUSABLE;' FROM DBA_INDEXES WHERE INDEX_TYPE='NORMAL' AND OWNER = 'DMHR' AND TABLE_NAME NOT LIKE '%_P_%';
--查询所有约束
SELECT TABLE_NAME,CONSTRAINT_NAME,STATUS FROM DBA_CONSTRAINTS WHERE OWNER = 'DMHR' AND TABLE_NAME NOT LIKE '%_P_%';
--TBL_MSV_SEND_SCHEME_DETAIL的外键FK_SENDDETAIL_REF_SEND默认为DISABLED状态