oracle 如何截断所有用户表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1696738/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to truncate all user tables?
提问by szaman
How can I truncate all user table in oracle? I have problem with tables constraints.
如何截断oracle中的所有用户表？我有表格约束问题。
回答by dgt
declare
begin
for c1 in (select table_name, constraint_name from user_constraints) loop
begin
execute immediate ('alter table '||c1.table_name||' disable constraint '||c1.constraint_name);
end;
end loop;
for t1 in (select table_name from user_tables) loop
begin
execute immediate ('truncate table '||t1.table_name);
end;
end loop;
for c2 in (select table_name, constraint_name from user_constraints) loop
begin
execute immediate ('alter table '||c2.table_name||' enable constraint '||c2.constraint_name);
end;
end loop;
end;
/
回答by Volker
Improved the above script in case you can't remove the constraint because dependencies exist (in the form of foreign keys that are dependent on this constraint - ORA-02297.) and by printing all (disable, truncate and enable) statements.
改进了上述脚本，以防您无法删除约束，因为依赖项存在（以依赖于此约束的外键形式 - ORA-02297。）并通过打印所有（禁用、截断和启用）语句。
set serveroutput on;
declare
begin
for c1 in (select y1.table_name, y1.constraint_name from user_constraints y1, user_tables x1 where x1.table_name = y1.table_name order by y1.r_constraint_name nulls last) loop
begin
dbms_output.put_line('alter table '||c1.table_name||' disable constraint '||c1.constraint_name || ';');
execute immediate ('alter table '||c1.table_name||' disable constraint '||c1.constraint_name);
end;
end loop;
for t1 in (select table_name from user_tables) loop
begin
dbms_output.put_line('truncate table '||t1.table_name || ';');
execute immediate ('truncate table '||t1.table_name);
end;
end loop;
for c2 in (select y2.table_name, y2.constraint_name from user_constraints y2, user_tables x2 where x2.table_name = y2.table_name order by y2.r_constraint_name nulls first) loop
begin
dbms_output.put_line('alter table '||c2.table_name||' enable constraint '||c2.constraint_name || ';');
execute immediate ('alter table '||c2.table_name||' enable constraint '||c2.constraint_name);
end;
end loop;
end;
回答by Ernst
Improvement in case you have special constraints which make above script fail:
改进，以防您有特殊限制使上述脚本失败：
set serveroutput on;
declare
begin
for c1 in (select y.table_name, y.constraint_name from user_constraints y, user_tables x where x.table_name = y.table_name) loop
begin
dbms_output.put_line('alter table '||c1.table_name||' disable constraint '||c1.constraint_name || ';');
execute immediate ('alter table '||c1.table_name||' disable constraint '||c1.constraint_name);
end;
end loop;
for t1 in (select table_name from user_tables) loop
begin
execute immediate ('truncate table '||t1.table_name);
end;
end loop;
for c2 in (select table_name, constraint_name from user_constraints) loop
begin
execute immediate ('alter table '||c2.table_name||' enable constraint '||c2.constraint_name);
end;
end loop;
end;
/
回答by Israel Margulies
You can output, then execute the one you like:
你可以输出，然后执行你喜欢的：
set serveroutput on;
begin
for r in (select table_name from user_tables) loop
dbms_output.put_line('truncate table ' || r.table_name);
end loop;
end;
回答by Khb
No need for variables
不需要变量
begin
for r in (select table_name from user_tables) loop
execute immediate 'truncate table ' || r.table_name;
end loop;
end;
Regards K
问候 K
