Oracle Drop User statement delete user from the database and remove all objects owned by the schema. This query will also remove user and contents i.e, schema objects from the recyclebin.
You should not delete sys/system user otherwise it will corrupt your database. You must have drop user privilege to remove user from the database. Its better you can connect with sysdba and drop a user.
Drop user in Oracle including contents
Dropping user in Oracle remove user and it’s contents from the database. You must use CASCADE keyword to remove all objects owned by the schema.
SQL> DROP USER barbie CASCADE;
Sometimes users are connected to the database and it takes long time to drop. So in this case you can drop forcefully by killing user session connected to the database.
SQL> select 'alter system kill session ''' || sid || ',' || serial# || ''' immediate;' from v$session where username ='&USERNAME'; SQL> DROP USER barbie CASCADE;
Conclusion: Drop user in Oracle including contents will remove all objects owned by the schema from the database. If drop user statement taking long time then user might be connected to the database and in this case you can remove user forcefully by killing the user sessions immediately.