oracle 如何删除所有用户表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1690404/
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 drop all user tables?
提问by szaman
How can I drop all user tables in oracle?
如何删除oracle中的所有用户表？
I have problem with constraints. When I disable all it is still no possible.
我有约束问题。当我禁用所有它仍然是不可能的。
回答by Henry Gao
BEGIN
FOR cur_rec IN (SELECT object_name, object_type
FROM user_objects
WHERE object_type IN
('TABLE',
'VIEW',
'MATERIALIZED VIEW',
'PACKAGE',
'PROCEDURE',
'FUNCTION',
'SEQUENCE',
'SYNONYM',
'PACKAGE BODY'
))
LOOP
BEGIN
IF cur_rec.object_type = 'TABLE'
THEN
EXECUTE IMMEDIATE 'DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '" CASCADE CONSTRAINTS';
ELSE
EXECUTE IMMEDIATE 'DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '"';
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('FAILED: DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '"'
);
END;
END LOOP;
FOR cur_rec IN (SELECT *
FROM all_synonyms
WHERE table_owner IN (SELECT USER FROM dual))
LOOP
BEGIN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM ' || cur_rec.synonym_name;
END;
END LOOP;
END;
/
回答by khylo
If you just want a really simple way to do this.. Heres a script I have used in the past
如果你只是想要一个非常简单的方法来做到这一点..这是我过去使用过的一个脚本
select 'drop table '||table_name||' cascade constraints;' from user_tables;
This will print out a series of drop commands for all tables in the schema. Spool the result of this query and execute it.
这将为模式中的所有表打印出一系列删除命令。假脱机这个查询的结果并执行它。
Source: https://forums.oracle.com/forums/thread.jspa?threadID=614090
来源：https: //forums.oracle.com/forums/thread.jspa?threadID=614090
Likewise if you want to clear more than tables you can edit the following to suit your needs
同样，如果您想清除的不仅仅是表格，您可以编辑以下内容以满足您的需要
select 'drop '||object_type||' '|| object_name || ';' from user_objects where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX')
回答by kazanaki
Another answer that worked for me is (credit to http://snipt.net/Fotinakis/drop-all-tables-and-constraints-within-an-oracle-schema/)
另一个对我有用的答案是（归功于http://snipt.net/Fotinakis/drop-all-tables-and-constraints-within-an-oracle-schema/）
BEGIN
FOR c IN (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE ('DROP TABLE "' || c.table_name || '" CASCADE CONSTRAINTS');
END LOOP;
FOR s IN (SELECT sequence_name FROM user_sequences) LOOP
EXECUTE IMMEDIATE ('DROP SEQUENCE ' || s.sequence_name);
END LOOP;
END;
Note that this works immediatelyafter you run it. It does NOTproduce a script that you need to paste somewhere (like other answers here). It runs directly on the DB.
请注意，这会在您运行后立即生效。它不是你需要（在这里像其他答案）粘贴的地方产生的脚本。它直接在数据库上运行。
回答by Md. Maksud Alam
begin
for i in (select 'drop table '||table_name||' cascade constraints' tbl from user_tables)
loop
execute immediate i.tbl;
end loop;
end;
回答by Brian
The simplest way is to drop the user that owns the objects with the cascade command.
最简单的方法是使用级联命令删除拥有对象的用户。
DROP USER username CASCADE
回答by TJR
The easiest way would be to drop the tablespace then build the tablespace back up. But I'd rather not have to do that. This is similar to Henry's except that I just do a copy/paste on the resultset in my gui.
最简单的方法是删除表空间，然后重新构建表空间。但我宁愿不必那样做。这与亨利的相似，除了我只是在我的 gui 中对结果集进行复制/粘贴。
SELECT
'DROP'
,object_type
,object_name
,CASE(object_type)
WHEN 'TABLE' THEN 'CASCADE CONSTRAINTS;'
ELSE ';'
END
FROM user_objects
WHERE
object_type IN ('TABLE','VIEW','PACKAGE','PROCEDURE','FUNCTION','SEQUENCE')
回答by ANiket Chavan
SELECT 'DROP TABLE "' || TABLE_NAME || '" CASCADE CONSTRAINTS;'
FROM user_tables;
user_tablesis a system table which contains all the tables of the user
the SELECT clause will generate a DROP statement for every table
you can run the script
user_tables是一个系统表，其中包含用户的所有表，SELECT 子句将为您可以运行脚本的每个表生成一个 DROP 语句
回答by Goyal Vicky
To remove all objects in oracle :
要删除 oracle 中的所有对象：
1) Dynamic
1) 动态
DECLARE
CURSOR IX IS
SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE ='TABLE'
AND OWNER='SCHEMA_NAME';
CURSOR IY IS
SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE
IN ('SEQUENCE',
'PROCEDURE',
'PACKAGE',
'FUNCTION',
'VIEW') AND OWNER='SCHEMA_NAME';
CURSOR IZ IS
SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('TYPE') AND OWNER='SCHEMA_NAME';
BEGIN
FOR X IN IX LOOP
EXECUTE IMMEDIATE('DROP '||X.OBJECT_TYPE||' SCHEMA_NAME.'||X.OBJECT_NAME|| ' CASCADE CONSTRAINT');
END LOOP;
FOR Y IN IY LOOP
EXECUTE IMMEDIATE('DROP '||Y.OBJECT_TYPE||' SCHEMA_NAME.'||Y.OBJECT_NAME);
END LOOP;
FOR Z IN IZ LOOP
EXECUTE IMMEDIATE('DROP '||Z.OBJECT_TYPE||' SCHEMA_NAME.'||Z.OBJECT_NAME||' FORCE ');
END LOOP;
END;
/
2)Static
2）静态
SELECT 'DROP TABLE "' || TABLE_NAME || '" CASCADE CONSTRAINTS;' FROM user_tables
union ALL
select 'drop '||object_type||' '|| object_name || ';' from user_objects
where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION')
union ALL
SELECT 'drop '
||object_type
||' '
|| object_name
|| ' force;'
FROM user_objects
WHERE object_type IN ('TYPE');
回答by Lova Chittumuri
Please follow the below steps.
请按照以下步骤操作。
begin
for i in (select 'drop table '||table_name||' cascade constraints' tb from user_tables)
loop
execute immediate i.tb;
end loop;
commit;
end;
purge RECYCLEBIN;
