Oracle – How to Completely Reset an Oracle Instance

oracle

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?

Best Answer

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)

Related Question