Differences between drop user and drop user cascade in Oracle

Drop User: only deletes users,

Drop user ×× cascade: All Tables and views under this user name are deleted.

User
Specify the user to be dropped. Oracle database does not drop users whose Schemas
Contain objects unless you specify cascade or unless you first explicitly drop
User's objects.

Cascade
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.

You can use the cascade parameter to delete all objects of the user. Note the following:

1. 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 a table exists in the schema, the primary key and foreign key related to the table are automatically deleted when the table is deleted.

2. 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.
If a table exists in the schema, indexes related to the table are automatically deleted when the table is deleted.

3. Oracle Database invalidates, but does not drop, the following objects in other schemas:
When a user is deleted, the following objects in other users will not be deleted, but will only be invalidated

3.1 views or synonyms for objects in the dropped user's schema
View, synonym

3.2 stored procedures, functions, or packages that query objects in the dropped user's schema
Stored Procedures, functions, and packages

4. 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.
Materialized views created by other users based on the deleted users are not deleted, but cannot be refreshed.

5. Oracle Database drops all triggers in the user's schema.
All triggers in user mode are deleted.

6. Oracle database does not drop roles created by the user.
Other users created by the deleted user will not be deleted.

------------------------------------------------------------------------------

Blog: http://blog.csdn.net/tianlesoftware

Online Resources: http://tianlesoftware.download.csdn.net

Video: http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx

Dba1 group: 62697716 (full); dba2 group: 62697977