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

PLSQL - Drop all database objects of a user

提问by NMan

I'm trying to use a procedure (no parameters) to drop all of the user-created database objects located within the schema from where the procedure is launched, but I'm really not sure on how to go about this. Here's what I have so far, but I think I'm going about this the wrong way.

我正在尝试使用一个过程（无参数）来删除位于启动该过程的模式中的所有用户创建的数据库对象，但我真的不确定如何去做。到目前为止，这是我所拥有的，但我认为我的做法是错误的。

create or replace procedure CLEAN_SCHEMA is
cursor schema_cur is
select 'drop '||object_type||' '|| object_name||  DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
from user_objects;
schema_rec schema_cur%rowtype;
begin
select 'drop '||object_type||' '|| object_name||  DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
into schema_rec
from user_objects;
end;
/

回答by Martin Brambley

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;

Create the above function (autonomous so DDL can be called via a function) then you can just:

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

select DROP_ALL_SCHEMA_OBJECTS from dual;

when you want to drop all your objects, make sure you dont try to drop the proc your running (i dont care about the procs thats why i dont have procs or functions in the object_type list)

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

if you want to drop everything you need an anonymous block

如果你想放弃一切你需要一个匿名块

but i needed to be able to do this from a tool that only allowed ansi sql (not plsql) hence a stored proc.

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

Enjoy.

享受。

回答by tamla83

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;

回答by Vijayan Srinivasan

Thanks Martin Brambley,

感谢马丁·布兰布利，

I feel we can simplify your answer in the following way.

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

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;

回答by Matthew Watson

Unless the user has hard to reapply permissions, its probably easier to just drop the user and recreate them.

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

回答by darreljnz

What you've got is a good start.

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

Here is the rest:

这是其余的：

• You have a cursor AND a select statement. You only need the cursor.
• Your next step is to call the drop statement using dynamic PLSQL. I'd use the EXECUTE IMMEDIATE statement. Its more elegant and preformance friendly to just select the name of the thing you're dropping and submit it as a bind variable to EXECUTE IMMEDIATE.
• In order to drop the objects of the schema calling the method and not the schema owning the method you have to use "AUTHID CURRENT_USER". See the Oracle documentationfor more info.
• Other things to drop: packages, functions, procedures (the system will likely hang then timeout if you try to drop this method while its running), Java classes, triggers, views, types

• 你有一个游标和一个选择语句。你只需要光标。
• 下一步是使用动态 PLSQL 调用 drop 语句。我会使用 EXECUTE IMMEDIATE 语句。只需选择您要删除的事物的名称并将其作为绑定变量提交给 EXECUTE IMMEDIATE，它就更加优雅和性能友好。
• 为了删除调用该方法的架构的对象而不是拥有该方法的架构，您必须使用“AUTHID CURRENT_USER”。有关更多信息，请参阅Oracle 文档。
• 其他要删除的东西：包、函数、过程（如果您在运行时尝试删除此方法，系统可能会挂起然后超时）、Java 类、触发器、视图、类型

Lastly, this is obviously a very dangerous method so you may want to consider putting it in a script instead of a stored procedure so it isn't left in the database for anyone to run.

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

回答by dpbradley

You're close - as someone else has noted you need an "EXECUTE IMMEDIATE" for the statement. You should consider:

你很接近 - 正如其他人所指出的，你需要一个“立即执行”的语句。你应该考虑：

• Instead of creating a procedure to do this, run this as an anonymous PL/SQL block so you don't have the issue of trying to drop a procedure that is running.

• Add a test for object type of TABLE and for that case modify the drop statement to include the cascade option to handle tables that are "parents" of other tables via foreign key constraints. Remember that you'll probably be generating the cursor list in an order that doesn't consider dependencies that will block the drop.

• Also on the subject of dependencies, it is probably best to drop tables first (add a DECODE in your cursor that assigns a lower numeric value to this object type and order the cursor select by this value). If you have Oracle objects of type TYPE that are used as column types in a table definition the table must be dropped first.

• If you use Oracle Advanced Queuing the objects related to this MUST be dropped with the AQ package API calls. Although you can drop the Oracle-generated tables for queue support with a regular DROP TABLE, you will find yourself in the catch-22 position of then not being able to drop the related queues nor add them back. Up to version 10g at least you couldn't even drop the containing schema without putting the database in a special mode when this situation existed

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

• 添加对 TABLE 对象类型的测试，并在这种情况下修改 drop 语句以包含级联选项，以通过外键约束处理作为其他表的“父”表的表。请记住，您可能会按不考虑会阻止删除的依赖项的顺序生成游标列表。

• 同样在依赖关系的问题上，最好先删除表（在光标中添加一个 DECODE，为该对象类型分配一个较低的数值，并按此值对光标选择进行排序）。如果您有 TYPE 类型的 Oracle 对象用作表定义中的列类型，则必须首先删除该表。

• 如果您使用 Oracle Advanced Queuing，则必须通过 AQ 包 API 调用删除与此相关的对象。尽管您可以使用常规的 DROP TABLE 删除 Oracle 生成的表以支持队列，但您会发现自己处于 catch-22 位置，无法删除相关队列，也无法将它们添加回来。至少在版本 10g 之前，如果存在这种情况，您甚至无法在不将数据库置于特殊模式的情况下删除包含模式

回答by xor

Thanks Martin Brambleyand Vijayan Srinivasan!

感谢Martin Brambley和Vijayan Srinivasan！

But Vijayan Srinivasan's version is not correct, because dependent objects of type 'TYPE' sometime generates errors during drop them:

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

ORA-02303: cannot drop or replace a type with type or table dependents

ORA-02303: 不能删除或替换具有类型或表依赖项的类型

My version drop ALL objects from Schema with additional:

我的版本从 Schema 中删除所有对象，并附加：

• drop procedures and functions (expect 'DROP_ALL_SCHEMA_OBJECTS')
• drop all jobs and dbms_jobs
• drop all db_links
• do not drop nested tables, because DROPing of nested tables not supported

• 删除过程和函数（期望'DROP_ALL_SCHEMA_OBJECTS'）
• 删除所有作业和 dbms_jobs
• 删除所有 db_links
• 不要删除嵌套表，因为不支持删除嵌套表

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;