How to completely "reset" an Oracle instance?

There are just so many things in the database in addition to user data that checking all of them would take a considerable amount of time. Also there are some parts that were not meant to be removed at all, for example the history of used features (for license audit).

Sure, you could try to check all non-default configuration or rerun catalog scripts (but whats the point of that? creating a new database does exactly that). Its easier to just drop and recreate in 15-30 minutes depending on your selected components.

PS: there is no reinstall involved in dropping and recreating a database.

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
FROM dba_users 
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.

  See Also: 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)}