如何截断所有用户表？

  • Z时代
  • 2024-01-10
  • 分类：问答

如何截断oracle中的所有用户表？我有表约束的问题。如何截断所有用户表？

回答：

无需变量

begin 
    for r in (select table_name from user_tables) loop 
    execute immediate 'truncate table ' || r.table_name; 
    end loop; 
end;

问候 ķ

回答：

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; 
/

回答：

可以输出，然后执行一个你喜欢的：万一

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;

回答：

改进你有特殊限制这使上面的脚本失败：

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; 
/

回答：

如果因为依赖关系存在（以依赖于此约束的外键的形式 - ORA-02297。）并通过打印all（disable，truncate和启用）语句。

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;

以上是 如何截断所有用户表？ 的全部内容， 来源链接： utcz.com/qa/262339.html

回到顶部