sql - 从 Oracle DB 删除数据

Question

我正在运行一些测试以将数据从一个数据库迁移到另一个数据库，为此我需要删除并重新创建相同的表、 View 和其他内容。那么 Oracle 中用于删除所有内容(删除表、 View 、序列、函数、过程等)的 SQL 语句是什么？我知道我可以使用“DROP”，但有时这还不够方便。

谢谢

Answer 1

最简单的方法是删除对象关联的模式:

DROP USER [schema name] CASCADE

从轨道上核对它 - 这是唯一确定的方法;)

对于您提供的脚本，您可以改为运行这些查询，而无需使用以下匿名过程生成中间脚本:

BEGIN

  --Bye Views!
  FOR i IN (SELECT uv.view_name
              FROM USER_VIEWS uv) LOOP
    EXECUTE IMMEDIATE 'drop view '|| i.view_name ||'';
  END LOOP;

  --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;

  --Bye Procedures/Functions/Packages!
  FOR i IN (SELECT us.name,
                   us.type
              FROM USER_SOURCE us
             WHERE us.type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE')
          GROUP BY us.name, us.type) LOOP
    EXECUTE IMMEDIATE 'drop '|| i.type ||' '|| i.name ||'';
  END LOOP;

  --Bye Synonyms!
  FOR i IN (SELECT ut.synonym_name
              FROM USER_SYNONYMS us
             WHERE us.synonym_name NOT LIKE 'sta%' 
               AND us.synonym_name LIKE 's_%') LOOP
    EXECUTE IMMEDIATE 'drop synonym '|| i.synonym_name ||'';
  END LOOP;

END;