PLSQL - 删除用户的所有数据库对象

7 回答

• 19

  create or replace
  FUNCTION                DROP_ALL_SCHEMA_OBJECTS RETURN NUMBER AS
  PRAGMA AUTONOMOUS_TRANSACTION;
  cursor c_get_objects is
    select object_type,'"'||object_name||'"'||decode(object_type,'TABLE' ,' cascade constraints',null) obj_name
    from user_objects
    where object_type in ('TABLE','VIEW','PACKAGE','SEQUENCE','SYNONYM', 'MATERIALIZED VIEW')
    order by object_type;
  cursor c_get_objects_type is
    select object_type, '"'||object_name||'"' obj_name
    from user_objects
    where object_type in ('TYPE');
  BEGIN
    begin
      for object_rec in c_get_objects loop
        execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name);
      end loop;
      for object_rec in c_get_objects_type loop
        begin
          execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name);
        end;
      end loop;
    end;
    RETURN 0;
  END DROP_ALL_SCHEMA_OBJECTS;
  

  创建上面的函数（自主，因此可以通过函数调用DDL）然后您可以：

  select DROP_ALL_SCHEMA_OBJECTS from dual;
  

  当你想要删除所有对象时，请确保你不要尝试删除你正在运行的proc（我不关心procs，这就是为什么我在object_type列表中没有procs或函数）

  如果你想丢弃所有你需要一个匿名块

  但我需要能够从一个只允许ansi sql（而不是plsql）的工具做到这一点，因此存储过程 .

  请享用 .

  回复于 2024-05-06T18:16:54+08:00
• 1

  declare
    cursor ix is
      select *
        from user_objects
       where object_type in ('TABLE', 'VIEW', 'FUNCTION', 'SEQUENCE');
  begin
   for x in ix loop
     execute immediate('drop '||x.object_type||' '||x.object_name);
   end loop;
  end;
  

  回复于 2024-05-06T18:16:54+08:00
• 2

  除非用户很难重新申请权限，否则可能更容易删除用户并重新创建它们 .

  回复于 2024-05-06T18:16:54+08:00
• 2

  谢谢Martin Brambley，

  我觉得我们可以通过以下方式简化您的答案 .

  CREATE OR REPLACE
  procedure  DROP_ALL_SCHEMA_OBJECTS AS
  PRAGMA AUTONOMOUS_TRANSACTION;
  cursor c_get_objects is
    select object_type,'"'||object_name||'"'||decode(object_type,'TABLE' ,' cascade constraints',null) obj_name
    FROM USER_OBJECTS
    where object_type in ('TABLE','VIEW','PACKAGE','SEQUENCE','SYNONYM', 'MATERIALIZED VIEW', 'TYPE')
    order by object_type;
  BEGIN
    begin
      for object_rec in c_get_objects loop
        execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name);
      end loop;
    end;
  END DROP_ALL_SCHEMA_OBJECTS;
  
  /
  
  execute DROP_ALL_SCHEMA_OBJECTS;
  

  回复于 2024-05-06T18:16:54+08:00
• 1

  你所拥有的是一个良好的开端 .

  其余部分如下：

  • 您有一个游标和一个select语句 . 你只需要光标 .

  • 下一步是使用动态PLSQL调用drop语句 . 我'd use the EXECUTE IMMEDIATE statement. Its more elegant and preformance friendly to just select the name of the thing you'重新丢弃并将其作为绑定变量提交给EXECUTE IMMEDIATE .

  • 为了删除调用方法的模式的对象而不是拥有该方法的模式，您必须使用"AUTHID CURRENT_USER" . 有关详细信息，请参阅the Oracle documentation .

  • 要删除的其他内容：包，函数，过程（如果在运行时尝试删除此方法，系统可能会挂起然后超时），Java类，触发器，视图，类型

  最后，这显然是一种非常危险的方法，因此您可能需要考虑将其放在脚本而不是存储过程中，这样它就不会留在数据库中供任何人运行 .

  回复于 2024-05-06T18:16:54+08:00
• 1

  你很接近 - 正如其他人已经注意到你需要一个“执行立即”的声明 . 你应该考虑：

  • 不要创建执行此操作的过程，而是将其作为匿名PL / SQL块运行，这样您就不会遇到尝试删除正在运行的过程的问题 .

  • 为TABLE的对象类型添加测试，对于该情况，修改drop语句以包含cascade选项，以通过外键约束处理作为其他表的“父”的表 . 请记住，您可能会按照不考虑阻止丢弃的依赖关系的顺序生成游标列表 .

  • 此外，关于依赖性的主题，最好先删除表（在游标中添加一个DECODE，为该对象类型分配较低的数值，并按此值对光标进行排序） . 如果您具有TYPE类型的Oracle对象，这些对象在表定义中用作列类型，则必须先删除该表 .

  • 如果使用Oracle Advanced Queuing，则必须使用AQ包API调用删除与此相关的对象 . 虽然您可以使用常规DROP TABLE删除Oracle生成的队列支持表，但您将发现自己处于catch-22位置，然后无法删除相关队列，也无法将其添加回来 . 至少版本10g至少你甚至无法在没有将数据库置于特殊模式时删除包含模式

  回复于 2024-05-06T18:16:54+08:00
• 13

  谢谢 Martin Brambley 和 Vijayan Srinivasan ！

  但是Vijayan Srinivasan的版本不正确，因为“TYPE”类型的依赖对象有时会在丢弃它们时产生错误：

  ORA-02303：不能删除或替换类型或表依赖的类型

  我的版本从Schema中删除所有对象以及其他：

  • drop程序和功能（期待'DROP_ALL_SCHEMA_OBJECTS'）

  • 删除所有作业和dbms_jobs

  • 删除所有db_links

  • 不删除嵌套表，因为不支持嵌套表的DROPing

  CREATE OR REPLACE
  procedure  DROP_ALL_SCHEMA_OBJECTS AS
  PRAGMA AUTONOMOUS_TRANSACTION;
  cursor c_get_objects is
    select uo.object_type object_type_2,'"'||uo.object_name||'"'||decode(uo.object_type,'TABLE' ,' cascade constraints',null) obj_name2
    FROM USER_OBJECTS uo
    where uo.object_type in ('TABLE','VIEW','PACKAGE','SEQUENCE','SYNONYM', 'MATERIALIZED VIEW', 'FUNCTION', 'PROCEDURE')
          and not (uo.object_type = 'TABLE' and exists (select 1 from user_nested_tables unt where uo.object_name = unt.table_name))
          and not (uo.object_type = 'PROCEDURE' and uo.object_name = 'DROP_ALL_SCHEMA_OBJECTS')
    order by uo.object_type;
  cursor c_get_objects_type is
    select object_type, '"'||object_name||'"' obj_name
    from user_objects
    where object_type in ('TYPE');
  cursor c_get_dblinks is
    select '"'||db_link||'"' obj_name
    from user_db_links;
  cursor c_get_jobs is
    select '"'||object_name||'"' obj_name
    from user_objects
    where object_type = 'JOB';
  cursor c_get_dbms_jobs is
    select job obj_number_id
    from user_jobs
    where schema_user != 'SYSMAN';
  BEGIN
    begin
      for object_rec in c_get_objects loop
        execute immediate ('drop '||object_rec.object_type_2||' ' ||object_rec.obj_name2);
      end loop;
      for object_rec in c_get_objects_type loop
        begin
          execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name);
        end;
      end loop;
      for object_rec in c_get_dblinks loop
          execute immediate ('drop database link '||object_rec.obj_name);
      end loop;
      for object_rec in c_get_jobs loop
          DBMS_SCHEDULER.DROP_JOB(job_name => object_rec.obj_name);
      end loop;
      commit;
      for object_rec in c_get_dbms_jobs loop
          dbms_job.remove(object_rec.obj_number_id);
      end loop;
      commit;
    end;
  END DROP_ALL_SCHEMA_OBJECTS;
  
  /
  
  execute DROP_ALL_SCHEMA_OBJECTS;
  drop procedure DROP_ALL_SCHEMA_OBJECTS;
  
  exit;
  

  回复于 2024-05-06T18:16:54+08:00