How to completely "reset" an Oracle instance?

Question

I'd like to drop all entities from an Oracle instance. I want to delete all tables, users, indices and so on, just like as if it would be a freshly created instance. The result should look, or at least closely approximate from the viewpoint of the clients, as if I had completely deleted and then reinstalled the instance with the same settings.

Doing this with a complete wipe/reinstall is slow, and it is not easily automatizable. I am looking for better options, but it is a possible solution, if there isn't.

Dropping the schemas is not enough, because some entities still remain in place (for example, users).

Is there an easy way to drop everything, to reach the initial clean and empty state, but more quickly?

Answer 1

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)}

Answer 2

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.

Answer 3

For Oracle 12c+, I recommend you drop the PDB and recreate it.