SQL：删除所有可用表中的所有数据

Question

我正在使用Oracle DB来维护超过30个表，我要如何删除所有表中的数据？我只想删除数据而不是删除表格。

- Lily

---

这个问题似乎不属于该网站的范围，因为它应该发表在 dba.stackexchange.com 上。 - user207421

Answer 1

没有'ALTER TABLE XXX DISABLE ALL CONSTRAINTS'这个命令。

我建议这样做;

BEGIN
  FOR c IN (SELECT table_name, constraint_name FROM user_constraints WHERE constraint_type = 'R')
  LOOP
    EXECUTE IMMEDIATE ('alter table ' || c.table_name || ' disable constraint ' || c.constraint_name);
  END LOOP;
  FOR c IN (SELECT table_name FROM user_tables)
  LOOP
    EXECUTE IMMEDIATE ('truncate table ' || c.table_name);
  END LOOP;
  FOR c IN (SELECT table_name, constraint_name FROM user_constraints WHERE constraint_type = 'R')
  LOOP
    EXECUTE IMMEDIATE ('alter table ' || c.table_name || ' enable constraint ' || c.constraint_name);
  END LOOP;
END;

- michaldo

---

这个解决方案可能不是最受欢迎的，因为人们不关心阅读这里的所有答案。 - user1073494

这应该是一个更好的解决方案。您还可以对其进行微调，以实际删除表和约束，而不是仅截断它们。 - Aditya T

Answer 2

生成一个脚本来截断（=删除所有行）所有表：

select 'truncate table ' || table_name || ';' from user_tables

然后执行脚本。

- Andomar

---

我已经编辑过这个回答了，很抱歉，但是 all_tables 这个东西非常危险。 - Neil Barnwell

针对新手需要注意的是：truncate 是非事务性操作，也就是说它无法被回滚。 - Jeffrey Kemp

如果定义了外键约束，则此方法将无法正常工作。 - René Nyffenegger

Answer 3

为了解决限制问题，可以使用类似以下的方法：

BEGIN

    FOR T in (SELECT table_name FROM user_tables) LOOP
      EXECUTE IMMEDIATE 'ALTER TABLE '||T.table_name||' DISABLE ALL CONSTRAINTS';
    END LOOP;

    FOR T in (SELECT table_name FROM user_tables) LOOP
      EXECUTE IMMEDIATE 'TRUNCATE TABLE '||T.table_name;
    END LOOP;

    FOR T in (SELECT table_name FROM user_tables) LOOP
      EXECUTE IMMEDIATE 'ALTER TABLE '||T.table_name||' ENABLE ALL CONSTRAINTS';
    END LOOP;
END;

- DCookie

---

如果表中没有定义约束条件，则alter table ... disable all constraints会抛出ORA-01735: invalid ALTER TABLE option，这将导致脚本无法截断每个表，如果至少有一个表没有约束条件。您可能希望在begin .. exception块中放置execute immediate。 - René Nyffenegger

Answer 4

使用截断的潜在缺点是可能会因引用完整性约束而失败。因此，您需要先禁用外键约束，然后执行截断，然后重新启用约束。 克隆模式（exp和imp）的“加号”是您还可以删除并重新创建表空间（如果要删除所有数据以释放一些物理磁盘空间，则可能需要这样做）。

- Gary Myers

Answer 5

克隆模式，然后删除旧表格？

- BCS

---

或者按照“真正的DBA”方式进行操作：备份完整的数据库，不包括数据，然后重建数据库（从主板开始...直到服务器）。 - BCS

@TomH。这有什么简单的？我不是DBA，它并没有告诉我要执行哪些命令。 - Eric Wilson

@FarmBoy，我记得这将取决于DBMS。 - BCS

@TomH。这是一个Oracle问题... - Eric Wilson

Answer 6

对Andomar的答案进行了轻微变化，以截断特定用户的所有表，而不仅仅是当前用户的表：

SELECT 'TRUNCATE TABLE ' || owner || '.' || table_name || ';' FROM all_tables WHERE owner = 'user/schema'

请将上面的user/schema替换为您感兴趣的用户/模式（在引号之间）的名称。

- Forage

---

非常感谢这个查询帮助了我这么多。如果你有多于一个数据库/模式，我更喜欢使用这个查询。 - payket

Answer 7

我使用上述提到的答案创建了这个存储过程。它完美地运行，没有任何错误或异常。

    create or replace PROCEDURE DELETE_ALL_DATA
AS 
cursor r1 is select * from user_constraints;
cursor r2 is select * from user_tables;
cursor r3 is select * from user_constraints;
cursor r4 is select * from user_tables;

BEGIN

    FOR c1 IN r1
  loop
    for c2 in r2
    loop
        begin
       if c1.table_name = c2.table_name and c1.status = 'ENABLED' THEN
        dbms_utility.exec_ddl_statement('alter table ' || c1.owner || '.' || c1.table_name || ' disable constraint ' || c1.constraint_name);
       end if;
        EXCEPTION
         WHEN NO_DATA_FOUND
           THEN
           continue;
         WHEN OTHERS 
           THEN
           continue;
           end;
    end loop;
  END LOOP;

    FOR T in (SELECT table_name FROM user_tables) LOOP
      begin
      EXECUTE IMMEDIATE 'TRUNCATE TABLE '||T.table_name;
      EXCEPTION
         WHEN NO_DATA_FOUND
           THEN
           continue;
         WHEN OTHERS 
           THEN
           continue;
           end;
    END LOOP;

    FOR c1 IN r3
  loop
    for c2 in r4
    loop
        begin
       if c1.table_name = c2.table_name and c1.status = 'DISABLED' THEN
        dbms_utility.exec_ddl_statement('alter table ' || c1.owner || '.' || c1.table_name || ' enable constraint ' || c1.constraint_name);
       end if;
        EXCEPTION
         WHEN NO_DATA_FOUND
           THEN
           continue;
         WHEN OTHERS 
           THEN
           continue;
           end;
    end loop;
  END LOOP;

    commit;
END DELETE_ALL_DATA;

- Ayush Aggarwal

Answer 8

删除Oracle中所有表的数据

DECLARE
  str VARCHAR2(100);
BEGIN
  FOR i IN
  (SELECT object_name FROM user_objects WHERE object_type='TABLE'
  )
  LOOP
    str := 'Truncate table '|| i.object_name;
    EXECUTE IMMEDIATE str;
    DBMS_OUTPUT.PUT_LINE('已删除表数据：' || i.object_name);
  END LOOP;
END;

更多信息请参见：http://www.oracleinformation.com/2014/10/delete-all-the-data-from-all-tables.html

- Brahmareddy K

Answer 9

这两行代码是最好的

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable 'DELETE FROM ?'
GO

- YOusaFZai

---

这是在Microsoft SQL Server数据库中截断所有表的最简单方法。即使您在删除数据后忘记重新启用约束条件。但是... 这个问题涉及Oracle... 而不是SQL Server。 - K4timini