Oracle - 在单个查询中删除多个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26968999/
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
Oracle - drop multiple table in a single query
提问by vim
I have fifty tables in a database, in that I need only six tables.
我在数据库中有五十张表，因为我只需要六张表。
How can I delete remaining tables by one single query?
如何通过一个查询删除剩余的表？
回答by DirkNM
You can generate a list of DROP TABLE commands with the query below:
您可以使用以下查询生成 DROP TABLE 命令列表：
SELECT 'DROP TABLE ' || table_name || ';' FROM user_tables;
After that you remove your six tables you want to keep and execute the other commands. Or you add a WHERE table_name NOT IN (...)clause to the query.
之后，删除要保留的六个表并执行其他命令。或者您WHERE table_name NOT IN (...)向查询添加子句。
Hope it helps.
希望能帮助到你。
回答by Deepu
Use something like this, as there is no direct command or way in oracle to do this
使用这样的东西，因为在 oracle 中没有直接的命令或方法来做到这一点
begin
for rec in (select table_name
from all_tables
where table_name like '%ABC_%'
)
loop
execute immediate 'drop table '||rec.table_name;
end loop;
end;
/
回答by Peter
To expand on the answer, for Oracle 10 versions and later, dropped tables are not deleted permanently, but moved to recycled bin. To truly delete tables need to add optional parameter PURGE.
为了扩展答案，对于 Oracle 10 版本及更高版本，删除的表不会永久删除，而是移动到回收站。要真正删除表需要添加可选参数PURGE。
Expanding on the accepted answer :
扩展已接受的答案：
SELECT 'DROP TABLE ' || table_name || ' PURGE ;' DB_DEATH FROM user_tables;
回答by Ankur R
First Run this query with table names that you want to keep.
首先使用要保留的表名运行此查询。
SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' )
AS statement FROM information_schema.tables
WHERE table_schema = 'mydatabase' AND table_name not in ('table1', 'table2', 'table3');
This query will give you DROP table query.
此查询将为您提供 DROP 表查询。
回答by Ankur R
- Select from the list on the left all the tables you want to drop. Drag and drop them in your worksheet. Select Object Name from the pop-up window
- Press the Edit menu and select Replace.
- Type in the find field the comma symbol
, - Type in the replace field the following text
;\ndrop table. Note that there is a space after the word table. Replace all. - Type
drop tablebefore your first table and;after your last one. - You are ready to drop your tables.
- 从左侧的列表中选择要删除的所有表。将它们拖放到工作表中。从弹出窗口中选择对象名称
- 按编辑菜单并选择替换。
- 在查找字段中输入逗号符号
, - 在替换字段中键入以下文本
;\ndrop table。注意单词table后面有一个空格。全部替换。 - 键入
drop table您的第一个表之前和;你的最后一个之后。 - 您已准备好放下桌子。
回答by Cristian Botelho
DECLARE
TYPE bulk_array is table of ALL_TABLES.TABLE_NAME%type;
array bulk_array;
BEGIN
SELECT OWNER ||'.'|| TABLE_NAME BULK COLLECT
INTO array
FROM ALL_TABLES
WHERE TABLE_NAME LIKE '%%';--HERE U WILL PUT YOUR CONDITIONS.
FOR i IN array.FIRST..array.LAST LOOP
EXECUTE IMMEDIATE 'DROP TABLE '|| array(i) || ' PURGE'; --Specify PURGE if you want to drop the table and release the space associated
END LOOP;
END;
