PLSQL 禁用所有约束，启用约束，索引，触发器等

--禁用外键和触发器

SET SERVEROUTPUT ON SIZE 50000
BEGIN
for c in (select 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints where CONSTRAINT_TYPE='R' or CONSTRAINT_TYPE='C') loop
DBMS_OUTPUT.PUT_LINE(C.V_SQL);
begin
EXECUTE IMMEDIATE c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;
for c in (select 'ALTER TABLE '||TNAME||' DISABLE ALL TRIGGERS ' AS v_sql from tab where tabtype='TABLE') loop
dbms_output.put_line(c.v_sql);
begin
execute immediate c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;
end;

--禁用索引

SET SERVEROUTPUT ON SIZE 50000
BEGIN
for c in (select 'ALTER INDEX ' ||index_name || ' unusable' AS v_sql
from user_indexes where table_owner='DBO_PWCDB' and index_type='NORMAL' and uniqueness='NONUNIQUE') loop
dbms_output.put_line(c.v_sql);
begin
execute immediate c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;
end;

--启用序列及触发器

create or replace procedure create_sequences
is
v_sql varchar2(4000);--动态sql语句
v_sequence SYS_REFCURSOR; --定义游标变量
v_row sequence_table%rowtype;--定义行级变量
v_next_num number(13);
v_max_num   number(13);
v_count number(10);
begin
    v_count:=0;
     --读取序列字典表
    open v_sequence for select * from sequence_table;
    loop
     v_count:=v_count+1;
     fetch v_sequence into v_row;
     exit when v_sequence%notfound;
     --查询表中主键的最大值
     v_sql := 'select max(' || v_row.primaryID || ') from ' || v_row.table_name;
     execute immediate v_sql into v_max_num;
     if (v_max_num is not null) then
       v_next_num := v_max_num + 1;
       --重新创建序列
       v_sql := 'create sequence ' || v_row.sequence_name || ' start with '|| v_next_num ||' increment by 1 nomaxvalue nocache';
       execute immediate v_sql;
       dbms_output.put_line(v_sql);
     end if;
    if (v_max_num is null) then
       v_max_num := 0;
       v_next_num := v_max_num + 1;
       --重新创建序列
       v_sql := 'create sequence ' || v_row.sequence_name || ' start with '|| v_next_num ||' increment by 1 nomaxvalue nocache';
       execute immediate v_sql;
       dbms_output.put_line(v_sql);
     end if;
    end loop;
    close v_sequence;
    dbms_output.put_line('已创建'||v_count||'个序列!');
end;

begin
create_sequences;
end;

-- 启用外键

SET SERVEROUTPUT ON SIZE 50000
begin
for c in (select 'ALTER TABLE '||TABLE_NAME||' ENABLE CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints where CONSTRAINT_TYPE='R' or CONSTRAINT_TYPE='C') loop
DBMS_OUTPUT.PUT_LINE(C.V_SQL);
begin
EXECUTE IMMEDIATE c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;
end;

巴特西

PLSQL 禁用所有约束，启用约束，索引，触发器等

最新文章

热门文章