How to delete all tables under the user by Oracle

This article will explain in detail how Oracle deletes all the tables under users. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

0. The most straightforward way:

Log in to Oracle with sys as sysdba first

> sql shutdown

> sql startup

Drop user username cascade

Create a new user

1 、

Select 'Drop table' | | table_name | |';'

From all_tables

User name to be deleted by where owner=' (uppercase)'

2 、

Delete all tables

Take user test as an example

For example:

Declare

Cursor cur1 is select table_name from dba_tables where owner='TEST'

Begin

For cur2 in cur1 loop

Execute immediate 'drop table test.' | | cur2.table_name

End loop

End

3. This deletes all objects of the current user (tables, views, triggers, stored procedures, functions)

Never tried.

DECLARE

TYPE name_list IS TABLE OF VARCHAR2 (40)

TYPE type_list IS TABLE OF VARCHAR2 (20)

Tab_name name_list:=name_list ()

Tab_type type_list:=type_list ()

Sql_str VARCHAR2 (500)

BEGIN

Sql_str: = 'select uo.object_name,uo.object_type from user_objects uo where uo.object_type not in (' 'INDEX'',''LOB'') order by uo.object_type desc'

EXECUTE IMMEDIATE sql_str BULK COLLECT INTO tab_name,tab_type

FOR i IN Tab_name.FIRST.. Tab_name.LAST LOOP

Sql_str: = 'DROP' | | Tab_type (I) | |''| | Tab_name (I)

EXECUTE IMMEDIATE sql_str

END LOOP

END

This is the end of the article on "how to delete all the tables under the Oracle". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.