oracle清空所有表数据

--1.禁用所有表外键约束

declare
? cursor c1 is
??? select t.table_name
????????? ,t.constraint_name
????? from user_constraints t
???? where t.owner = 'EMS3'
?????? AND T.constraint_type = 'R';
? stmt varchar2(4000);
begin
? for cc in c1 loop
??? BEGIN
???
????? stmt := 'alter table ' || cc.table_name || ' disable constraint ' ||
????????????? cc.constraint_name;
????? dbms_output.put_line(stmt);
????? execute immediate stmt;
??? EXCEPTION
????? WHEN OTHERS THEN
??????? dbms_output.put_line('error ' || stmt);
??? end;
? end loop;
end;

?

--2.启用所有表外键约束

declare
? cursor c1 is
??? select t.table_name
????????? ,t.constraint_name
????? from user_constraints t
???? where t.owner = 'EMS3'
?????? AND T.constraint_type = 'R';
? stmt varchar2(4000);
begin
? for cc in c1 loop
??? BEGIN
???
????? stmt := 'alter table ' || cc.table_name || ' enable constraint ' ||
????????????? cc.constraint_name;
????? dbms_output.put_line(stmt);
????? execute immediate stmt;
??? EXCEPTION
????? WHEN OTHERS THEN
??????? dbms_output.put_line('error ' || stmt);
??? end;
? end loop;
end;

?

--3.清空表数据

declare
? cursor c1 is
??? select table_name
????? from user_tables t
???? where t.TABLE_NAME not in ('ROLE_FUNC',
??????????????????????????????? 'USER_ROLE',
??????????????????????????????? 'ROLE_DICT_TYPE',
??????????????????????????????? 'DICT_TYPE_APPLY',
??????????????????????????????? 'EXAM_TYPE',
??????????????????????????????? 'TEMPLATE_RULE_DICT',
??????????????????????????????? 'T_DICT_ITEM_APPLY',
??????????????????????????????? 'PAY_GATE_TYPE',
??????????????????????????????? 'PAY_GATE',
??????????????????????????????? 'DICT_TYPE',
??????????????????????????????? 'DICT_ITEM',
??????????????????????????????? 'DEPT_INFO',
??????????????????????????????? 'ACCOUNT',
??????????????????????????????? 'FUNC',
??????????????????????????????? 'ROLE');
? stmt varchar2(4000);
begin
? for cc in c1 loop
??? BEGIN
????? stmt := 'TRUNCATE table ' || cc.table_name;
????? dbms_output.put_line(stmt);
????? execute immediate stmt;
??? EXCEPTION
????? WHEN OTHERS THEN
??????? dbms_output.put_line('error ' || stmt);
??? end;
? end loop;
end;