•Never attempt to drop the users SYS or SYSTEM. Doing so will corrupt your database.
•To drop a user you must have the DROP USER system privilege. This is
SQL> grant drop user to arju;
You can whether user currently have DROP USER privilege or not by,
SQL> select * from session_privs where privilege='DROP USER';
•To drop a user named test use DROP USER TEST unless inside test schema there is no objects.
•If test schema contains any objects then to drop the user you must use DROP USER TEST CASCADE. If you don't use CASCADE option it will throw ORA-01922: Example in case of user named A.
Create objects inside schema A.
SQL> create table a.test(a number);
SQL> drop user a;
drop user a
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop 'A'
As there is objects inside schema A so use CASCADE clause to drop.
SQL> drop user a cascade;
•After dropping a user if the user's schema contains tables, then Oracle Database drops the tables and automatically drops any referential integrity constraints on tables in other schemas that refer to primary and unique keys on these tables.
•After dropping a user the objects in other schema which are views, synonyms. stored procedures, functions, or packages that query objects in the dropped user's schema become invalid but don't drop.
•Oracle Database does not drop materialized views in other schemas that are based on tables in the dropped user's schema. However, because the base tables no longer exist, the materialized views in the other schemas can no longer be refreshed.
•When you drop a user, Oracle Database also purges all of that user's schema objects from the recycle bin.
•It is very good to remember that dropping user in oracle Database does not drop roles created by the user.
•You can't drop a user that is currently connected. If you try to drop a current connected user you will get ORA-01940.
SQL> drop user arju;
drop user arju
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
If you want to drop a connected user first disconnect it. It is demonstrated in section