In Oracle, a schema is normally linked to a user. So if you drop a user and specify the
CASCADEparameter, then all objects related to that user will be dropped.
You can query the users table with the following query:
SELECT 'drop user ' || username || ' cascade;', oracle_maintained
WHERE oracle_maintained = 'N';
This will generate a list of statements for users which aren't created by the system (Oracle) and which, when executed, will drop all users and their related objects. Example:
You are left with an Oracle instance that is pretty clean.
There are objects which will be maintained by the Oracle instance and which might take further combing to remove.
Specify CASCADE to drop all objects in the user's schema before dropping the user. You must specify this clause to drop a user whose schema contains any objects.
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.
If this clause results in tables being dropped, then the database also drops all domain indexes created on columns of those tables and invokes appropriate drop routines.
Oracle Database Data Cartridge Developer's Guide for more information on these routines
Oracle Database invalidates, but does not drop, the following objects in other schemas:
- Views or synonyms for objects in the dropped user's schema
- Stored procedures, functions, or packages that query objects in the dropped user's schema
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.
- Oracle Database drops all triggers in the user's schema.
- Oracle Database does not drop roles created by the user.
Reference: DROP USER (Oracle | Database SQL Language Reference)