Learn how to use the Oracle DROP USER statement with syntax and examples.
The DROP USER statement is used to remove a user from the Oracle database and remove all objects owned by that user.
The syntax for the DROP USER statement in Oracle/PLSQL is:
DROP USER user_name [ CASCADE ];
user_name is the name of the user to remove from the Oracle database.
CASCADE is optional. If user_name owns any objects (ie: tables or views in its schema), you must specify CASCADE to drop all of these objects.
Let's look at a simple DROP USER statement.
If the user does not own any objects in its schema, you could execute the following DROP USER statement:
DROP USER smithj;
This would drop the user called smithj. This DROP USER statement will only run if smithj does not own any objects in its schema.
If smithj did own objects in its schema, you would need to run the following DROP USER statement instead:
DROP USER smithj CASCADE;
This DROP USER statement would remove the user smithj, drop all objects (ie: tables and views) owned by smithj, and all referential integrity constraints on smithj's objects would also be dropped.