SQL 删除 Oracle 中的所有用户表/序列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2549718/
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
Dropping all user tables/sequences in Oracle
提问by Ambience
As part of our build process and evolving database, I'm trying to create a script which will remove all of the tables and sequences for a user. I don't want to do recreate the user as this will require more permissions than allowed.
作为我们构建过程和不断发展的数据库的一部分，我正在尝试创建一个脚本，它将删除用户的所有表和序列。我不想重新创建用户，因为这将需要比允许的更多的权限。
My script creates a procedure to drop the tables/sequences, executes the procedure, and then drops the procedure. I'm executing the file from sqlplus:
我的脚本创建了一个删除表/序列的过程，执行该过程，然后删除该过程。我正在从 sqlplus 执行文件：
drop.sql:
删除.sql:
create or replace procedure drop_all_cdi_tables
is
cur integer;
begin
cur:= dbms_sql.OPEN_CURSOR();
for t in (select table_name from user_tables) loop
execute immediate 'drop table ' ||t.table_name|| ' cascade constraints';
end loop;
dbms_sql.close_cursor(cur);
cur:= dbms_sql.OPEN_CURSOR();
for t in (select sequence_name from user_sequences) loop
execute immediate 'drop sequence ' ||t.sequence_name;
end loop;
dbms_sql.close_cursor(cur);
end;
/
execute drop_all_cdi_tables;
/
drop procedure drop_all_cdi_tables;
/
Unfortunately, dropping the procedure causes a problem. There seems to cause a race condition and the procedure is dropped before it executes.
E.g.:
不幸的是，删除程序会导致问题。似乎会导致竞争条件，并且该过程在执行之前就被删除了。
例如：
SQL*Plus: Release 11.1.0.7.0 - Production on Tue Mar 30 18:45:42 2010 Copyright (c) 1982, 2008, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Procedure created. PL/SQL procedure successfully completed. Procedure created. Procedure dropped. drop procedure drop_all_user_tables * ERROR at line 1: ORA-04043: object DROP_ALL_USER_TABLES does not exist SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64 With the Partitioning, OLAP, Data Mining and Real Application Testing options
Any ideas on how to get this working?
关于如何让这个工作的任何想法？
回答by OMG Ponies
If you're not intending on keeping the stored procedure, I'd use an anonymous PLSQL block:
如果您不打算保留存储过程，我会使用匿名 PLSQL 块：
BEGIN
--Bye Sequences!
FOR i IN (SELECT us.sequence_name
FROM USER_SEQUENCES us) LOOP
EXECUTE IMMEDIATE 'drop sequence '|| i.sequence_name ||'';
END LOOP;
--Bye Tables!
FOR i IN (SELECT ut.table_name
FROM USER_TABLES ut) LOOP
EXECUTE IMMEDIATE 'drop table '|| i.table_name ||' CASCADE CONSTRAINTS ';
END LOOP;
END;
回答by Gary Myers
For an SQL statement, the semi-colon at the end will execute the statement. The / will execute the previous statement. As such, you end lines of
对于 SQL 语句，末尾的分号将执行该语句。/ 将执行前面的语句。因此，您结束了
drop procedure drop_all_cdi_tables;
/
will drop the procedure, then try to drop it again.
将删除该过程，然后再次尝试删除它。
If you look at your output, you'll see 'PROCEDURE CREATED', then executed, then 'PROCEDURE CREATED' again as it re-executes the last statement (EXECUTE is a SQL*Plus command, not a statement so isn't buffered) then "PROCEDURE DROPPED" and then it tries (and fails) to drop it the second time.
如果您查看输出，您将看到“PROCEDURE CREATED”，然后执行，然后再次执行“PROCEDURE CREATED”，因为它重新执行最后一条语句（EXECUTE 是 SQL*Plus 命令，不是语句，因此没有缓冲) 然后“删除程序”，然后它尝试（但失败）第二次删除它。
PS. I agree with Dougman on the odd DBMS_SQL calls.
附注。我同意 Dougman 对奇怪的 DBMS_SQL 调用的看法。
回答by Ahmed Elgamal
Just run these two statements and then run all the results:
只需运行这两个语句，然后运行所有结果：
select 'drop table ' || table_name || ';' from user_tables;
select 'drop sequence ' || sequence_name || ';' from user_sequences;
回答by Doug Porter
It looks like your example error message is getting an error on drop_all_user_tablesbut the example you gave is for drop_all_cdi_tables. Does the drop_all_user_tablescode look different?
看起来您的示例错误消息出现错误，drop_all_user_tables但您提供的示例是针对drop_all_cdi_tables. 请问drop_all_user_tables代码看看有什么不同？
Also you have calls to dbms_sqlbut don't seem to be using it do any parsing.
你也有调用dbms_sql但似乎没有使用它做任何解析。
回答by bravenoob
In addition to the solution presented by OMG Ponies, if you have sequences with blank spaces, you need to enhance the PLSQL a bit:
除了 OMG Ponies 提出的解决方案之外，如果您有带有空格的序列，则需要稍微增强 PLSQL：
BEGIN
FOR i IN (SELECT sequence_name FROM user_sequences)
Loop
EXECUTE IMMEDIATE('"DROP SEQUENCE ' || user || '"."' || i.sequence_name || '"');
End Loop;
End;
/
回答by ZerosAndOnes
For some reason OMG Ponies solution gave an error "SQL command not properly ended" on PLSQL. In case someone else comes across the same problem, here is how I was able to delete all the tables in the current schema.
出于某种原因，OMG Ponies 解决方案在 PLSQL 上给出了错误“SQL 命令未正确结束”。如果其他人遇到同样的问题，这里是我如何能够删除当前模式中的所有表。
DECLARE
table_name VARCHAR2(30);
CURSOR usertables IS SELECT * FROM user_tables WHERE table_name NOT LIKE 'BIN$%';
BEGIN
FOR i IN usertables
LOOP
EXECUTE IMMEDIATE 'drop table ' || i.table_name || ' cascade constraints';
END LOOP;
END;
/
Credits: Snippler
学分：狙击手
