SQL 在 Oracle 中删除模式中的所有内容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29926262/
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
Delete all contents in a schema in Oracle
提问by zerosssa
Is possible to delete all contents in scheme in Oracle? I found this script:
是否可以删除Oracle中方案中的所有内容？我找到了这个脚本：
Begin
for c in (select table_name from user_tables) loop
execute immediate ('drop table '||c.table_name||' cascade constraints);
end loop;
End;
But I would like to know if are there anything to drop everything in the schema, indexes,tables,contraints... but not the schema (drop user ...).
但是我想知道是否有任何东西可以删除架构，索引，表，约束中的所有内容......但不是架构（删除用户......）。
Thanks.
谢谢。
回答by venki
Normally, it is simplest to drop and add the user. This is the preferred method if you have system or sysdba access to the database.
通常，删除和添加用户是最简单的。如果您具有对数据库的 system 或 sysdba 访问权限，则这是首选方法。
If you don't have system level access, and want to scrub your schema, the following sql will produce a series of drop statments, which can then be executed.
如果您没有系统级别的访问权限，并且想要清理您的架构，则以下 sql 将生成一系列 drop 语句，然后可以执行这些语句。
select 'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS','') || ';' from user_objects
Then, I normally purge the recycle bin to really clean things up. To be honest, I don't see a lot of use for oracle's recycle bin, and wish i could disable it, but anyway:
然后，我通常会清除回收站以真正清理东西。老实说，我看不到 oracle 的回收站有多大用处，希望我可以禁用它，但无论如何：
purge recyclebin;
This will produce a list of drop statements. Not all of them will execute - if you drop with cascade, dropping the PK_* indices will fail. But in the end, you will have a pretty clean schema. Confirm with:
这将生成一个 drop 语句列表。并非所有这些都会执行 - 如果您使用级联删除，则删除 PK_* 索引将失败。但最终，您将拥有一个非常干净的架构。确认：
select * from user_objects
Also, just to add, the Pl/sql block in your question will delete only tables, it doesn't delete all other objects.
另外，只是补充一下，您问题中的 Pl/sql 块将仅删除表，而不会删除所有其他对象。
ps: Copied from some website, was useful to me. Tested and working like a charm.
ps：从某个网站复制过来的，对我很有用。测试和工作就像一个魅力。
回答by Bacteria
Yes you can. You can drop the user and thus drop the schema objects. The DROP USER statement is used to remove a user from the Oracle database and remove all objects owned by that user.
是的你可以。您可以删除用户，从而删除架构对象。DROP USER 语句用于从 Oracle 数据库中删除用户并删除该用户拥有的所有对象。
DROP USER TestDB;
This statement will only run properly and drop the user called TestDB only if TestDB does not own any objects in its schema. Object in the sense tables and views etc. If it contains any objects then after executing the DROP USER statement you will get the below error message
只有当 TestDB 在其架构中不拥有任何对象时，此语句才会正常运行并删除名为 TestDB 的用户。意义表和视图等中的对象。如果它包含任何对象，则在执行 DROP USER 语句后，您将收到以下错误消息
Error starting at line : 1 in command -
DROP USER TestDB
Error report -
SQL Error: ORA-01922: CASCADE must be specified to drop 'TESTDB'
01922. 00000 - "CASCADE must be specified to drop '%s'"
*Cause: Cascade is required to remove this user from the system. The
user own's object which will need to be dropped.
*Action: Specify cascade.
If TestDB did own objects in its schema, you would need to run the following DROP USER statement instead:
如果 TestDB 在其架构中确实拥有对象，则需要运行以下 DROP USER 语句：
DROP USER TestDB CASCADE;
This statement will drop all objects owned by TestDB, and all referential integrity constraints on TestDB objects would also be dropped.
此语句将删除 TestDB 拥有的所有对象，并且还将删除 TestDB 对象上的所有引用完整性约束。
回答by Heri
Found the following script on github which worked out-of-the-box (SQL*Plus: Release 12.2.0.1.0 Production):
在 github 上找到以下脚本，该脚本开箱即用（SQL*Plus：Release 12.2.0.1.0 Production）：
https://gist.github.com/rafaeleyng/33eaef673fc4ee98a6de4f70c8ce3657
https://gist.github.com/rafaeleyng/33eaef673fc4ee98a6de4f70c8ce3657
Thanks to the author Rafael Eyng.
感谢作者 Rafael Eyng。
Just login into the schema whose objects you want to drop.
只需登录到要删除其对象的模式即可。
BEGIN
FOR cur_rec IN (SELECT object_name, object_type
FROM user_objects
WHERE object_type IN
('TABLE',
'VIEW',
'PACKAGE',
'PROCEDURE',
'FUNCTION',
'SEQUENCE',
'SYNONYM'
))
LOOP
BEGIN
IF cur_rec.object_type = 'TABLE'
THEN
EXECUTE IMMEDIATE 'DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '" CASCADE CONSTRAINTS';
ELSE
EXECUTE IMMEDIATE 'DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '"';
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( 'FAILED: DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '"'
);
END;
END LOOP;
END;
/
回答by Pancho
The following SQLplus script generates the SQL statements needed to delete all schema objects from the desired user:
以下 SQLplus 脚本生成从所需用户删除所有架构对象所需的 SQL 语句：
set heading off
set pagesize 0
set feedback off
-- wipe out all scheduler jobs
select 'exec dbms_scheduler.drop_job(job_name => '''||j.job_creator||'.'||j.job_name||''');'
from user_scheduler_jobs j
/
-- wipe out all XML schemas
select 'exec dbms_xmlschema.deleteSchema(schemaURL => '''||s.qual_schema_url||''',delete_option => dbms_xmlschema.DELETE_CASCADE_FORCE);'
from user_xml_schemas s
/
-- wipe out all remaining objects
select 'drop '
||o.object_type
||' '||object_name
||case o.object_type when 'TABLE' then ' cascade constraints' when 'TYPE' then ' force' else '' end
||';'
from user_objects o
where o.object_type not in ('JOB','LOB','PACKAGE BODY','INDEX','TRIGGER')
and not exists (select 1
from user_objects r
where r.object_name = o.object_name
and r.object_type = 'MATERIALIZED VIEW'
and o.object_type != 'MATERIALIZED VIEW'
)
/
-- empty the recycle bin
select 'purge recyclebin;' from dual
/
The script works 100% for me as is - but if for some reason it is not complete for you then it is easily enhanced using a virtual machine (VM) as follows:
该脚本按原样 100% 对我有效 - 但如果由于某种原因它对您来说不完整，那么它可以使用虚拟机 (VM) 轻松增强，如下所示：
- log on as [your schema user to empty]
- Take a snapshot of your VM
- run the above script to create the deletion statements
- run the deletion statements (you can ignore any "object does not exist" errors, as some objects will be automatically removed prior to the script removal statement. This occurs as a result of owning objects being removed)
- log off
- log on as SYS and execute "drop user [your schema user to empty];" -- WITHOUT the cascade option
- 以 [您的架构用户为空] 身份登录
- 拍摄 VM 的快照
- 运行上面的脚本来创建删除语句
- 运行删除语句（您可以忽略任何“对象不存在”错误，因为某些对象将在脚本删除语句之前自动删除。这是因为拥有对象被删除而发生的）
- 注销
- 以 SYS 身份登录并执行“drop user [您的架构用户为空]；” -- 没有级联选项
If step 6 fails then you need to identify the remaining objects preventing your user from being deleted and add them to the above script. Repeat until your user drops (ie. your script is comprehensive) then save your script
如果第 6 步失败，那么您需要确定阻止您的用户被删除的剩余对象并将它们添加到上述脚本中。重复直到你的用户掉线（即你的脚本很全面）然后保存你的脚本
Roll back your VM to your snapshot and repeat steps 3 and 4 (using your updated script) - and you should now have a 100% empty schema.
将 VM 回滚到快照并重复第 3 步和第 4 步（使用更新后的脚本） - 现在您应该拥有一个 100% 空架构。
回答by John Taylor
This is what I used:
这是我使用的：
set echo off feedback off serverout on
spool drop_all_objects.sql
declare l_object varchar2(32000);
begin
for i in (select object_name, object_type from dba_objects where owner='<owner>') loop
if i.object_type='JOB' then
l_object := 'begin dbms_scheduler.drop_job (job_name => ''<owner>'||i.object_name||'''); end;';
elsif i.object_type='PROGRAM' then
l_object := 'begin dbms_scheduler.drop_program (program_name => ''<owner>'||i.object_name||'''); end;';
elsif i.object_type='RULE' then
l_object := 'begin dbms_rule_adm.drop_rule (rule_name => ''<owner>'||i.object_name||''', force => TRUE); end;';
elsif i.object_type='RULE SET' then
l_object := 'begin dbms_rule_adm.drop_rule_set (rule_set_name => ''<owner>'||i.object_name||''', delete_rules => TRUE); end;';
elsif i.object_type='CHAIN' then
l_object := 'begin dbms_scheduler.drop_chain (chain_name => ''<owner>'||i.object_name||''', force => TRUE); end;';
elsif i.object_type='RULE' then
l_object := 'begin dbms_rule_adm.drop_evaluation_context (evaluation_context_name => ''<owner>'||i.object_name||''', force => TRUE); end;';
else
l_object := 'drop '||i.object_type||'<owner>'||i.object_name||';';
end if;
dbms_output.put_line(i_object);
dbms_output.put_line('/');
end loop;
end;
/
@drop_all_objects
回答by Tomok
Just go with:
只是去：
select 'drop '||object_type||' '|| object_name || ';' from user_objects where
object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX')
