oracle 删除模式的所有对象
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42174345/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dropping all objects of a schema
提问by Srikant
The query below will drop all the tables which are present in the current user A's schema (normal scenario).
下面的查询将删除当前用户 A 的模式中存在的所有表（正常情况）。
select 'drop '||object_type||' '|| object_name || ';'
from user_objects
where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX');
But what if this query is run by the DBA with a SYS or SYSDBA login? What objects are present in the user_objects view when logged in using sys/sysdba user? Will it drop all the tables of all the schemas in the database or will the query throw an error? Intention is to drop only objects of Schema 'A'.
但是，如果这个查询是由 DBA 以 SYS 或 SYSDBA 登录名运行的呢？使用 sys/sysdba 用户登录时，user_objects 视图中存在哪些对象？它会删除数据库中所有模式的所有表还是查询会抛出错误？意图是仅删除架构“A”的对象。
采纳答案by Alex Poole
The user_objectsview has the current user's objects, so if run as SYS it would try to drop SYS's objects - very bad news, as it would destroy your database. You can read about the three versions of tthat view in the documentation.
该user_objects视图具有当前用户的对象，因此如果以 SYS 身份运行，它将尝试删除 SYS 的对象 - 非常糟糕的消息，因为它会破坏您的数据库。您可以在文档中了解 tthat 视图的三个版本。
For SYS to see another user's objects you should look at the dba_objectsview instead, filtering on the user you're interested in; and include the target schema (owner) in the drop statement too:
为了让 SYS 看到另一个用户的对象，您应该查看dba_objects视图，过滤您感兴趣的用户；并在 drop 语句中包含目标模式（所有者）：
select 'drop ' || object_type || ' "' || owner || '"."' || object_name || '";'
from dba_objects
where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX')
and owner = 'A';
I've also included wrapping the object name (and less usefully the owner) in double quotes, in case there are any objects that were created with quoted identifiers.
我还包括用双引号将对象名称（以及不太有用的所有者）括起来，以防有任何使用带引号的标识符创建的对象。
If you included tables in the query and tried to run the output you might get errors from trying to drop dependent obects in the wrong order, i.e. dropping a parent table before its children. There are object types too, but if you ultimately want to drop everything, it might be simpler to drop and recreate the user - capturing and recreating their privileges too.
如果您在查询中包含表并尝试运行输出，您可能会因尝试以错误的顺序删除相关对象而出错，即在其子项之前删除父表。也有对象类型，但是如果您最终想要删除所有内容，删除并重新创建用户可能会更简单 - 也可以捕获和重新创建他们的权限。
回答by ibre5041
No when executed as SYS it will destroy your database. Try this. It will prompt 3 times for schema name to be cleared.
不，当以 SYS 身份执行时，它会破坏您的数据库。试试这个。它将提示 3 次清除架构名称。
Beware this script might get stuck in a infinite loop, if you have curious object type in your schema (like SCHEDULER CHAIN - for example)
请注意，如果您的架构中有奇怪的对象类型（例如 SCHEDULER CHAIN - 例如），此脚本可能会陷入无限循环
set serveroutput on size unlimited
declare
v_ItemCount integer;
begin
SELECT count(*)
INTO v_ItemCount
FROM ALL_OBJECTS AO
WHERE AO.OWNER = '&USER'
AND AO.OBJECT_TYPE NOT IN ('INDEX', 'LOB')
AND AO.OBJECT_NAME NOT LIKE 'BIN$%';
while (v_ItemCount > 0) loop
for v_Cmd in (SELECT 'drop ' || AO.OBJECT_TYPE || ' ' || '"'||AO.OWNER||'"'|| '.' || '"'||AO.OBJECT_NAME||'"' ||
DECODE(AO.OBJECT_TYPE,
'TABLE',
' CASCADE CONSTRAINTS',
'') as DROPCMD,
AO.OWNER,
AO.OBJECT_TYPE,
AO.OBJECT_NAME
FROM ALL_OBJECTS AO
WHERE AO.OWNER = '&USER'
AND AO.OBJECT_TYPE NOT IN ('INDEX', 'LOB')
AND AO.OBJECT_NAME NOT LIKE 'BIN$%')
loop
begin
if v_Cmd.OBJECT_TYPE = 'SCHEDULE' then
DBMS_SCHEDULER.DROP_SCHEDULE('"'||v_Cmd.OWNER||'"'||'.'||'"'||v_Cmd.OBJECT_NAME||'"', true);
ELSIF v_Cmd.OBJECT_TYPE = 'JOB' then
DBMS_SCHEDULER.DROP_JOB('"'||v_Cmd.OWNER||'"'||'.'||'"'||v_Cmd.OBJECT_NAME||'"', true);
ELSIF v_Cmd.OBJECT_TYPE = 'PROGRAM' then
DBMS_SCHEDULER.DROP_PROGRAM('"'||v_Cmd.OWNER||'"'||'.'||'"'||v_Cmd.OBJECT_NAME||'"', true);
else
execute immediate v_Cmd.dropcmd;
end if;
dbms_output.put_line(v_Cmd.dropcmd);
exception
when others then
null; -- ignore errors
end;
end loop;
SELECT count(*)
INTO v_ItemCount
FROM ALL_OBJECTS AO
WHERE AO.OWNER = '&USER'
AND AO.OBJECT_TYPE NOT IN ('INDEX','LOB')
AND AO.OBJECT_NAME NOT LIKE 'BIN$%';
end loop;
execute immediate 'purge dba_recyclebin';
end;
回答by Feng Zhang
FYI, the query below only generates all the sql, then you need to run the generated sql to actually get things done.
仅供参考，下面的查询仅生成所有 sql，然后您需要运行生成的 sql 才能真正完成任务。
Code Snippet (with tables included):
代码片段（包括表格）：
select 'drop '||object_type||' '|| object_name || ';'
from user_objects
where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX', 'TABLE');
select 'drop '||object_type||' '|| object_name || ';'
from user_objects
where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX', 'TABLE');
