oracle启用和禁用触发器

最新推荐文章于 2025-07-29 05:16:30 发布

levin_li

最新推荐文章于 2025-07-29 05:16:30 发布

阅读量374

点赞数

分类专栏： Database 文章标签： oracle constraints triggers table sql 脚本

Database 专栏收录该内容

65 篇文章

订阅专栏

本文提供Oracle数据库中触发器及各类约束（如主键、唯一键等）的批量禁用与启用脚本。通过PL/SQL块实现，帮助快速进行数据库维护操作。

oracle启用和禁用触发器

DBA_constraints.CONSTRAINT_TYPE=P表示Primary key 主键
DBA_constraints.CONSTRAINT_TYPE=U表示Unique key 唯一索引

DBA_constraints.CONSTRAINT_TYPE=C表示Check 约束

DBA_constraints.CONSTRAINT_TYPE=R表示Foreign Key 外键

--禁用脚本
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 SIZE 10000
BEGIN
for c in (select 'ALTER TABLE '||TABLE_NAME||' ENABLE 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||' ENABLE 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;
/