Oracle deletes all tables, views, sequences, functions, stored procedures, and packages of the current user.

-- I have tried the following methods 1 and 3. It is best to delete them in sqlplus. The restoration method is:
002
-- Write the delete table as a script:
003
--************************
004
-- ** Method 1
005
--************************
006
-- Enter the following commands on the terminal in sequence:
007
Sqlplus
008
-- Enter the username to be deleted
009
-- Enter the password
010
SET HEAD OFF
011
SPOOL/tmp/database/drop_tables. SQL
012
 
013
-- Chr (13) ASCII = newline chr (10) ASCII = carriage return
014
-- Delete tables
015
Select 'drop table' | table_name | ';' | chr (13) | chr (10) from user_tables;
016
-- Delete views
017
Select 'drop view' | view_name | ';' | chr (13) | chr (10) from user_views;
018
 
019
 
020
-- Delete seqs
021
Select 'drop sequence '| sequence_name |'; '| chr (13) | chr (10) from user_sequences;
022
 
023
 
024
-- Delete functions
025
Select 'drop function' | object_name | ';' | chr (13) | chr (10) from user_objects where object_type = 'function ';
026
 
027
 
028
-- Delete procedure
029
Select 'drop procedure '| object_name |'; '| chr (13) | chr (10) from user_objects where object_type = 'Procedure ';
030
 
031
 
032
-- Delete package
033
Select 'drop package' | object_name | ';' | chr (13) | chr (10) from user_objects where object_type = 'package ';
034
 
035
-- Delete trigger
036
SELECT 'drop TRIGGER "'| SYS_CONTEXT ('userenv', 'current _ user') | '". "'| TRIGGER_NAME |'"; '| CHR (13) | CHR (10) FROM USER_TRIGGERS
037
 
038
 
039
Spool off;
040
@/Tmp/database/drop_tables. SQL;
041
 
042
Purge recyclebin; -- clear the Oracle Recycle Bin
043
 
044
-- Example: (delete all tables of the user asus under win)
045
Sqlplus
046
Asus-User Name
047
Asus-Password
048
SET HEAD OFF
049
SPOOL c:/drop_tables. SQL -- save as an SQL statement
050
 
051
-- Chr (13) ASCII = newline chr (10) ASCII = carriage return
052
-- Delete tables
053
Select 'drop table' | table_name | ';' | chr (13) | chr (10) from user_tables;
054
-- Delete views
055
Select 'drop view' | view_name | ';' | chr (13) | chr (10) from user_views;
056
-- Delete seqs
057
Select 'drop sequence '| sequence_name |'; '| chr (13) | chr (10) from user_sequences;
058
-- Delete functions
059
Select 'drop function' | object_name | ';' | chr (13) | chr (10) from user_objects where object_type = 'function ';
060
-- Delete procedure
061
Select 'drop procedure '| object_name |'; '| chr (13) | chr (10) from user_objects where object_type = 'Procedure ';
062
-- Delete package
063
Select 'drop package' | object_name | ';' | chr (13) | chr (10) from user_objects where object_type = 'package ';
064
-- Delete trigger
065
SELECT 'drop TRIGGER "'| SYS_CONTEXT ('userenv', 'current _ user') | '". "'| TRIGGER_NAME |'"; '| CHR (13) | CHR (10) FROM USER_TRIGGERS
066
 
067
Spool off;
068
@ C:/drop_tables. SQL;
069
Purge recyclebin; -- clear the Oracle Recycle Bin
070
Quit -- exit sqlplus
071
 
072
-- Complete
073
 
074
-- Then restore the data.
075
-- Imp maximo/maximo @ orcl fromuser = maximo touser = maximo file = 21: 14: 34 seconds. dmp tablespaces = tablespaces
076
 
077
 
078
--************************
079
-- ** Method 2
080
--************************
081
1 select Drop table | table_name |;
082
From all_tables
083
Where owner = username to be deleted (uppercase );
084
2,
085
Delete all tables
086
Take user test as an Example
087
For example:
088
Declare
089
Cursor cur1 is select table_name from dba_tables where owner = TEST;
090
Begin
091
For cur2 in cur1 loop
092
Execute immediate drop table test. | cur2.table _ name;
093
End loop;
094
End;
095
3. Delete all objects (tables, views, triggers, stored procedures, and functions) of the current user)
096
DECLARE
097
TYPE name_list is table of VARCHAR2 (40 );
098
TYPE type_list is table of VARCHAR2 (20 );
099
Tab_name name_list: = name_list ();
100
Tab_type type_list: = type_list ();
101
SQL _str VARCHAR2 (500 );
102
BEGIN
103
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;
104
Execute immediate SQL _str BULK COLLECT INTO tab_name, tab_type;
105
FOR I IN Tab_name.FIRST .. Tab_name.LAST LOOP
106
SQL _str: = DROP | Tab_type (I) | Tab_name (I );
107
Execute immediate SQL _str;
108
End loop;
109
END;
110
 
111
 
112
 
113
--************************
114
-- ** Method 3
115
-- ** Use Oracle SQL Developer
116
--************************
117
-- Log in with the user to be deleted
118
-- Enter the following content in the worksheet:

From Jiang zhongzheng's blog