SQL：从所有可用表中删除所有数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/832160/
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
SQL: delete all the data from all available tables
提问by Lily
I am using oracle DB to maintain more than 30 tables, how can I delete all the data from all the tables? I only want to delete the data but not drop the tables.
我使用oracle DB维护了30多个表，如何将所有表中的数据全部删除？我只想删除数据但不想删除表。
回答by michaldo
There is no command 'ALTER TABLE XXX DISABLE ALL CONSTRAINTS'
没有命令“ALTER TABLE XXX DISABLE ALL CONSTRAINTS”
I propose this;
我提出这个；
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;
回答by Andomar
Generate a script to truncate (= remove all rows from) all tables:
生成一个脚本来截断（= 从中删除所有行）所有表：
select 'truncate table ' || table_name || ';' from user_tables
And then execute the script.
然后执行脚本。
回答by DCookie
To address the issue of constraints, something like this should work:
为了解决约束问题，这样的事情应该有效：
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;
回答by Gary Myers
The potential drawback with a truncate is that it may fail on referential integrity constraints. So you'd want to disable foreign key constraints first, then do the truncate, then re-enable constraints. The 'plus' with cloning the schema (exp and imp) is that you could also drop and recreate the tablespace too (which you may want to do if you want to reclaim some physical disk space as a result of removing all the data).
截断的潜在缺点是它可能会在引用完整性约束上失败。因此，您首先要禁用外键约束，然后进行截断，然后重新启用约束。克隆模式（exp 和 imp）的“加号”是您也可以删除并重新创建表空间（如果您想因删除所有数据而回收一些物理磁盘空间，您可能想要这样做）。
回答by BCS
Clone the schema and then drop the old tables?
克隆模式然后删除旧表？
回答by Ayush Aggarwal
I created this stored proc, using the answers mentioned above. This works perfectly without any errors or exceptions.
我使用上面提到的答案创建了这个存储过程。这完美无缺，没有任何错误或异常。
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;
回答by Forage
A slight variation on Andomar's answer to truncate all tables for a specific user instead of only those of the current user:
Andomar 对截断特定用户的所有表而不是仅截断当前用户的所有表的回答略有不同：
SELECT 'TRUNCATE TABLE ' || owner || '.' || table_name || ';' FROM all_tables WHERE owner = 'user/schema'
Replace the user/schemabit above with the name of the user/schema (between the quotes) you are interested in.
用user/schema您感兴趣的用户/模式（在引号之间）的名称替换上面的位。
回答by Brahmareddy K
Delete all the data from all tables in oracle
删除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('table data deleted :' || i.object_name);
END LOOP;
END;
For more info: http://www.oracleinformation.com/2014/10/delete-all-the-data-from-all-tables.html
更多信息：http: //www.oracleinformation.com/2014/10/delete-all-the-data-from-all-tables.html
回答by YOusaFZai
these two line script are the best
这两行脚本是最好的
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'DELETE FROM ?'
GO
