首页 文章

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

提问于
浏览 次
30

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

我的脚本创建了一个删除表/序列的过程，执行过程，然后删除过程 . 我正在从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 - 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

关于如何使这个工作的任何想法？

sql oracle plsql build-process oracle11g

6 回答

  • 73

    如果您're not intending on keeping the stored procedure, I'使用anonymous PLSQL block：

    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;
    回复于
  • 6

    对于SQL语句，末尾的分号将执行该语句 . /将执行前一个语句 . 因此，你结束了

    drop procedure drop_all_cdi_tables;
/

    将删除该程序，然后尝试再次删除它 .

    如果你查看输出，你会看到'PROCEDURE CREATED'，然后执行，然后'PROCEDURE CREATED'再次重新执行最后一个语句（EXECUTE是一个SQL * Plus命令，而不是一个语句所以没有缓冲）然后“程序删除”，然后它尝试（并失败）第二次丢弃它 .

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

    回复于
  • 1

    看起来您的示例错误消息在 drop_all_user_tables 上收到错误，但您给出的示例是 drop_all_cdi_tables . drop_all_user_tables 代码看起来有什么不同吗？

    你也可以调用 dbms_sql 但似乎没有使用它做任何解析 .

    回复于
  • 2

    除了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;
/
    回复于
  • 0

    出于某种原因，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

    回复于
  • 0

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

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

相关问题