blocks|key|1198837|text|BEGIN
+++FOR+cur_rec+IN+(SELECT+object_name,+object_type
+++++++++++++++++++FROM+user_objects
+++++++++++++++++++WHERE+object_type+IN
+++++++++++++++++++++++++++++('TABLE',
++++++++++++++++++++++++++++++'VIEW',
++++++++++++++++++++++++++++++'MATERIALIZED+VIEW',
++++++++++++++++++++++++++++++'PACKAGE',
++++++++++++++++++++++++++++++'PROCEDURE',
++++++++++++++++++++++++++++++'FUNCTION',
++++++++++++++++++++++++++++++'SEQUENCE',
++++++++++++++++++++++++++++++'SYNONYM',
++++++++++++++++++++++++++++++'PACKAGE+BODY'
+++++++++++++++++++++++++++++))
+++LOOP
++++++BEGIN
+++++++++IF+cur_rec.object_type+=+'TABLE'
+++++++++THEN
++++++++++++EXECUTE+IMMEDIATE+'DROP+'
++++++++++++++++++++++++++++++%7C%7C+cur_rec.object_type
++++++++++++++++++++++++++++++%7C%7C+'+"'
++++++++++++++++++++++++++++++%7C%7C+cur_rec.object_name
++++++++++++++++++++++++++++++%7C%7C+'"+CASCADE+CONSTRAINTS';
+++++++++ELSE
++++++++++++EXECUTE+IMMEDIATE+'DROP+'
++++++++++++++++++++++++++++++%7C%7C+cur_rec.object_type
++++++++++++++++++++++++++++++%7C%7C+'+"'
++++++++++++++++++++++++++++++%7C%7C+cur_rec.object_name
++++++++++++++++++++++++++++++%7C%7C+'"';
+++++++++END+IF;
++++++EXCEPTION
+++++++++WHEN+OTHERS
+++++++++THEN
++++++++++++DBMS_OUTPUT.put_line+('FAILED:+DROP+'
++++++++++++++++++++++++++++++++++%7C%7C+cur_rec.object_type
++++++++++++++++++++++++++++++++++%7C%7C+'+"'
++++++++++++++++++++++++++++++++++%7C%7C+cur_rec.object_name
++++++++++++++++++++++++++++++++++%7C%7C+'"'
+++++++++++++++++++++++++++++++++);
++++++END;
+++END+LOOP;
+++FOR+cur_rec+IN+(SELECT+*+
+++++++++++++++++++FROM+all_synonyms+
+++++++++++++++++++WHERE+table_owner+IN+(SELECT+USER+FROM+dual))
+++LOOP
++++++BEGIN
+++++++++EXECUTE+IMMEDIATE+'DROP+PUBLIC+SYNONYM+'+%7C%7C+cur_rec.synonym_name;
++++++END;
+++END+LOOP;
END;
/|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1198838|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>BEGIN
 FOR cur_rec IN (SELECT object_name, object_type
 FROM user_objects
 WHERE object_type IN
 ('TABLE',
 'VIEW',
 'MATERIALIZED VIEW',
 'PACKAGE',
 'PROCEDURE',
 'FUNCTION',
 'SEQUENCE',
 'SYNONYM',
 'PACKAGE BODY'
 ))
 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;
 FOR cur_rec IN (SELECT * 
 FROM all_synonyms 
 WHERE table_owner IN (SELECT USER FROM dual))
 LOOP
 BEGIN
 EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM ' || cur_rec.synonym_name;
 END;
 END LOOP;
END;
/
</code></pre>

blocks|key|1414059|text|最简单的方法是使用cascade命令删除拥有对象的用户。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1414060|DROP+USER+username+CASCADE|code-block|syntax|javascript|1414061|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The simplest way is to drop the user that owns the objects with the cascade command.

<pre><code>DROP USER username CASCADE
</code></pre>

blocks|key|1414093|text|最简单的方法是删除表空间，然后重新构建表空间。但我不想这么做。这与Henry的类似，只是我只是在gui中对结果集进行了复制/粘贴。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1414094|SELECT
++'DROP'
++,object_type
++,object_name
++,CASE(object_type)
+++++WHEN+'TABLE'+THEN+'CASCADE+CONSTRAINTS;'
+++++ELSE+';'
+++END
+FROM+user_objects
+WHERE
+++object_type+IN+('TABLE','VIEW','PACKAGE','PROCEDURE','FUNCTION','SEQUENCE')|code-block|syntax|javascript|1414095|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The easiest way would be to drop the tablespace then build the tablespace back up. But I'd rather not have to do that. This is similar to Henry's except that I just do a copy/paste on the resultset in my gui.

<pre><code>SELECT
 'DROP'
 ,object_type
 ,object_name
 ,CASE(object_type)
 WHEN 'TABLE' THEN 'CASCADE CONSTRAINTS;'
 ELSE ';'
 END
 FROM user_objects
 WHERE
 object_type IN ('TABLE','VIEW','PACKAGE','PROCEDURE','FUNCTION','SEQUENCE')
</code></pre>

blocks|key|1198956|text|如果你只是想要一种非常简单的方法..这是我过去用过的一个脚本|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1198957|select+'drop+table+'%7C%7Ctable_name%7C%7C'+cascade+constraints;'+from+user_tables;|code-block|syntax|javascript|1198958|这将为模式中的所有表打印出一系列drop命令。假脱机这个查询的结果并执行它。|1198959|来源：https://forums.oracle.com/forums/thread.jspa?threadID=614090|offset|length|1198960|同样，如果要清除的不只是表，可以编辑以下内容以满足您的需要|1198961|select+'drop+'%7C%7Cobject_type%7C%7C'+'%7C%7C+object_name+%7C%7C+';'+from+user_objects+where+object_type+in+('VIEW','PACKAGE','SEQUENCE',+'PROCEDURE',+'FUNCTION',+'INDEX')|1198962|entityMap|0|LINK|mutability|MUTABLE|url|https://forums.oracle.com/forums/thread.jspa?threadID=614090^0|0|0|0|3|1O|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|10|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|11|8|@]|9|@$K|12|L|13|1|14]]|A|$]]|$1|M|3|N|5|6|7|15|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|16|8|@]|9|@]|A|$E|F]]|$1|Q|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|X]]]]

If you just want a really simple way to do this.. Heres a script I have used in the past

<pre><code>select 'drop table '||table_name||' cascade constraints;' from user_tables;
</code></pre>

This will print out a series of drop commands for all tables in the schema. Spool the result of this query and execute it.

Source: <a href="https://forums.oracle.com/forums/thread.jspa?threadID=614090">https://forums.oracle.com/forums/thread.jspa?threadID=614090</a>

Likewise if you want to clear more than tables you can edit the following to suit your needs

<pre><code>select 'drop '||object_type||' '|| object_name || ';' from user_objects where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX')
</code></pre>

blocks|key|1414139|text|begin
++for+i+in+(select+'drop+table+'%7C%7Ctable_name%7C%7C'+cascade+constraints'+tbl+from+user_tables)+
++loop
+++++execute+immediate+i.tbl;
++end+loop;
end;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1414140|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>begin
 for i in (select 'drop table '||table_name||' cascade constraints' tbl from user_tables) 
 loop
 execute immediate i.tbl;
 end loop;
end;
</code></pre>

blocks|key|1414154|text|SELECT+'DROP+TABLE+"'+%7C%7C+TABLE_NAME+%7C%7C+'"+CASCADE+CONSTRAINTS;'+
FROM+user_tables;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1414155|user_tables是一个系统表，其中包含用户的所有表。SELECT子句将为您可以运行脚本的每个表生成一个DROP语句|unstyled|offset|length|style|CODE|1414156|entityMap^0|0|0|B|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|N|8|@$G|O|H|P|I|J]]|9|@]|A|$]]|$1|K|3|-4|5|F|7|Q|8|@]|9|@]|A|$]]]|L|$]]

<pre><code>SELECT 'DROP TABLE "' || TABLE_NAME || '" CASCADE CONSTRAINTS;' 
FROM user_tables;
</code></pre>

<code>user_tables</code> is a system table which contains all the tables of the user
the SELECT clause will generate a DROP statement for every table
you can run the script

blocks|key|1414193|text|要删除oracle中的所有对象，请执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1414194|1)动态|1414195|DECLARE
CURSOR+IX+IS
SELECT+*+FROM+ALL_OBJECTS+WHERE+OBJECT_TYPE+='TABLE'+
AND+OWNER='SCHEMA_NAME';
+CURSOR+IY+IS
+SELECT+*+FROM+ALL_OBJECTS+WHERE+OBJECT_TYPE+
IN+('SEQUENCE',
'PROCEDURE',
'PACKAGE',
'FUNCTION',
'VIEW')+AND++OWNER='SCHEMA_NAME';
+CURSOR+IZ+IS
+SELECT+*+FROM+ALL_OBJECTS+WHERE+OBJECT_TYPE+IN+('TYPE')+AND++OWNER='SCHEMA_NAME';
BEGIN
+FOR+X+IN+IX+LOOP
+++EXECUTE+IMMEDIATE('DROP+'%7C%7CX.OBJECT_TYPE%7C%7C'+SCHEMA_NAME.'%7C%7CX.OBJECT_NAME%7C%7C+'+CASCADE+CONSTRAINT');
+END+LOOP;
+FOR+Y+IN+IY+LOOP
+++EXECUTE+IMMEDIATE('DROP+'%7C%7CY.OBJECT_TYPE%7C%7C'+SCHEMA_NAME.'%7C%7CY.OBJECT_NAME);
+END+LOOP;
+FOR+Z+IN+IZ+LOOP
+++EXECUTE+IMMEDIATE('DROP+'%7C%7CZ.OBJECT_TYPE%7C%7C'+SCHEMA_NAME.'%7C%7CZ.OBJECT_NAME%7C%7C'+FORCE+');
+END+LOOP;
END;
/|code-block|syntax|javascript|1414196|2)静态|1414197|++++SELECT+'DROP+TABLE+"'+%7C%7C+TABLE_NAME+%7C%7C+'"+CASCADE+CONSTRAINTS;'+FROM+user_tables
++++++++union+ALL
++++++++select+'drop+'%7C%7Cobject_type%7C%7C'+'%7C%7C+object_name+%7C%7C+';'+from+user_objects+
++++++++where+object_type+in+('VIEW','PACKAGE','SEQUENCE',+'PROCEDURE',+'FUNCTION')
++++++++union+ALL
++++++++SELECT+'drop+'
++++++++%7C%7Cobject_type
++++++++%7C%7C'+'
++++++++%7C%7C+object_name
++++++++%7C%7C+'+force;'
++++++++FROM+user_objects
++++++++WHERE+object_type+IN+('TYPE');|1414198|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

To remove all objects in oracle : 

1) Dynamic

<pre><code>DECLARE
CURSOR IX IS
SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE ='TABLE' 
AND OWNER='SCHEMA_NAME';
 CURSOR IY IS
 SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE 
IN ('SEQUENCE',
'PROCEDURE',
'PACKAGE',
'FUNCTION',
'VIEW') AND OWNER='SCHEMA_NAME';
 CURSOR IZ IS
 SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('TYPE') AND OWNER='SCHEMA_NAME';
BEGIN
 FOR X IN IX LOOP
 EXECUTE IMMEDIATE('DROP '||X.OBJECT_TYPE||' SCHEMA_NAME.'||X.OBJECT_NAME|| ' CASCADE CONSTRAINT');
 END LOOP;
 FOR Y IN IY LOOP
 EXECUTE IMMEDIATE('DROP '||Y.OBJECT_TYPE||' SCHEMA_NAME.'||Y.OBJECT_NAME);
 END LOOP;
 FOR Z IN IZ LOOP
 EXECUTE IMMEDIATE('DROP '||Z.OBJECT_TYPE||' SCHEMA_NAME.'||Z.OBJECT_NAME||' FORCE ');
 END LOOP;
END;
/
</code></pre>

2)Static

<pre><code> SELECT 'DROP TABLE "' || TABLE_NAME || '" CASCADE CONSTRAINTS;' FROM user_tables
 union ALL
 select 'drop '||object_type||' '|| object_name || ';' from user_objects 
 where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION')
 union ALL
 SELECT 'drop '
 ||object_type
 ||' '
 || object_name
 || ' force;'
 FROM user_objects
 WHERE object_type IN ('TYPE');
</code></pre>

blocks|key|1199054|text|请按照以下步骤操作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1199055|begin
++for+i+in+(select+'drop+table+'%7C%7Ctable_name%7C%7C'+cascade+constraints'+tb+from+user_tables)+
++loop
+++++execute+immediate+i.tb;
++end+loop;
++commit;
end;
purge+RECYCLEBIN;|code-block|syntax|javascript|1199056|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Please follow the below steps.

<pre><code>begin
 for i in (select 'drop table '||table_name||' cascade constraints' tb from user_tables) 
 loop
 execute immediate i.tb;
 end loop;
 commit;
end;
purge RECYCLEBIN;
</code></pre>

How can I drop all user tables in oracle?

I have problem with constraints. When I disable all it is still no possible.

How to drop all user tables?

如何删除oracle中的所有用户表？我有约束方面的问题。当我禁用所有的时候，这仍然是不可能的。

问如何删除所有用户表？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何删除所有用户表？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何删除所有用户表？
EN