How does Oracle delete database users?

Example: delete user abc,

First, log on to the database as a sysdba user.

$ Sqlplus/as sysdba

Delete User abc

SQL> drop user abc cascade

Note: If the user abc is deleted, the schema objects under the user is deleted, and the corresponding tablespace is not deleted.

Delete all tables of a specified user
1,
Select 'drop table' | table_name | ';'
From all_tables
Where owner = 'username to be deleted (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. Delete all objects (tables, views, triggers, stored procedures, and functions) of the current user)
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;