在Oracle中删除所有用户表/序列

Question

作为我们的构建过程和不断发展的数据库的一部分，我正在尝试创建一个脚本，以删除用户的所有表和序列。我不想重新创建用户，因为这将需要比允许的更多的权限。

我的脚本创建了一个过程来删除表/序列，执行该过程，然后删除该过程。我从sqlplus中执行该文件：

drop.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;
/

很遗憾，删除此存储过程会导致问题。似乎会引起竞争条件，并在执行之前删除该过程。
例如：

SQL*Plus: Release 11.1.0.7.0 - 生产环境于 2010年3月30日 星期二 18时45分42秒
版权所有 (c) 1982, 2008，Oracle。保留所有权利。
已连接到： 
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64位生产版本
包含分区、OLAP、数据挖掘和实用测试选项
过程创建完成。
PL/SQL 过程已成功完成。
过程创建完成。
过程已被删除。
drop procedure drop_all_user_tables
*
第 1 行出现错误：
ORA-04043: 对象 DROP_ALL_USER_TABLES 不存在
SQL> 已断开与 Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64
位生产版本的连接，包含分区、OLAP、数据挖掘和实用测试选项。

有什么办法可以解决这个问题吗？

- Ambience

Answer 1

如果你不打算保留存储过程，我会使用匿名PL/SQL块：

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;

- OMG Ponies

---

这个能解决问题。奇怪的是，我必须在脚本末尾添加一个斜杠才能执行匿名PLSQL块。如果我们稍后创建一个自定义的MSBUILD任务来执行脚本中的语句 - 斜杠会引起问题吗？ - Ambience

/符号告诉sqlplus你的PLSQL块已经完成，并将其提交给数据库进行处理。因此，如果你的MSBUILD使用sqlplus，它将需要这个/符号。 - Todd Pierce

@OMGPonies 我正在尝试从Oracle SQL Developer执行相同的命令，但是我收到错误消息：ORA-00701: object necessary for warmstarting database cannot be altered以及其他错误。有什么提示吗？ - lbrahim

Answer 2

只需运行这两个语句，然后运行所有结果：

select 'drop table ' || table_name || ';' from user_tables;
select 'drop sequence ' || sequence_name || ';' from user_sequences;

- Ahmed Elgamal

Answer 3

在SQL语句中，结尾的分号将执行该语句。而斜杠将执行前一个语句。因此，您可以使用这些符号来结束行的内容。

drop procedure drop_all_cdi_tables;
/

将删除该过程，然后再次尝试删除。

如果您查看输出，您会看到“PROCEDURE CREATED”，然后执行，然后再次出现“PROCEDURE CREATED”，因为它重新执行了最后一个语句（EXECUTE是SQL * Plus命令，不是语句，因此不被缓冲），然后“PROCEDURE DROPPED”，然后它尝试（并失败）第二次删除它。

PS. 我同意Dougman关于奇怪的DBMS_SQL调用的观点。

- Gary Myers

Answer 4

似乎你的错误信息出现在drop_all_user_tables上，但是你给出的示例为drop_all_cdi_tables。请问drop_all_user_tables代码是否不同？

另外，你调用了dbms_sql，但似乎没有用它进行任何解析。

- Doug Porter

---

drop_all_cdi_tables 是相同的代码。dbms_sql是从另一个示例中借用的 - 我是一个 P/L SQL 新手 :) - Ambience

Answer 5

除了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;
/

- bravenoob

Answer 6

由于某种原因，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;
/

鸣谢：Snippler

- ZerosAndOnes