Oracle Drop/Delete User with All tables and files

Hi All,

Today I am going to explain the procedure to delete/drop a user in Oracle database along with tables and mapped files for that user tablespace.

Pre-requisites

Oracle database with the following

1. User
2. Tablespace of the user
3. Datafile of the tablespace

Procedure

Drop the user

Syntax: DROP USER CASCADE;
Example: DROP USER sampleuser CASCADE;

Make the tablespace offline

Syntax: ALTER TABLESPACE OFFLINE;
Example: ALTER TABLESPACE sampletablespace OFFLINE;

Drop the tablespace with mapped datafile[s]

Syntax: DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES
Example: DROP TABLESPACE sampletablespace INCLUDING CONTENTS AND DATAFILES

With the above 3 commands, you can make sure that, user and user related data has been completed removed from database.

Hope, it will help to resolve your issue.

Please send your feedback and comments to psrdotcom@gmail.com