禁用外键脚本

1、ORACLE数据库中的外键约束名都在表user_constraints中可以查到。其中constraint_type='R'

表示是外键约束。

2、启用外键约束的命令为：alter table table_name enable constraint constraint_name

3、禁用外键约束的命令为：alter table table_name disable constraint constraint_name

4、然后再用SQL查出数据库中所以外键的约束名：

select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R'

select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R'

--禁用脚本(外键，触发器)

SET SERVEROUTPUT ON SIZE 10000

BEGIN

for c in (select 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINT '|| constraint_name||' ' as v_sql from user_constraints where CONSTRAINT_ TYPE='R') 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

declare

v_name varchar2(32767);

n_count number(10) := 0;

begin

dbms_output.enable(100000);

for rec_name in (select * from user_tables) loop

v_name := v_name||','||rec_name.table_name;

n_count := n_count + 1;

-- if MOD(n_count, 10)=0 then dbms_output.put_line(v_name); v_name := '';

-- end if;

end loop;

dbms_output.put_line(v_name); end;