Drop User in Oracle

•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;
Grant succeeded.

You can whether user currently have DROP USER privilege or not by,
SQL> select * from session_privs where privilege='DROP USER';
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);
Table created.

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;
User dropped

•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
Troubleshot ORA-01940

Related Documents:
-----------------------
Troubleshot ORA-01940