How to delete a user in Oracle 10 including all it's tablespace and datafiles

Question

When I give the command to drop a user i.e. DROP USER 'username' cascade,

1. Does it deletes all the tablespace and datafiles used by that particular user.

2. If not, what is the command to delete all the tablespace / datafiles / disk space that were used by that particular user.

Answer 1

After dropping the user, you need to, for each related tablespace, take it offline and drop it. For example if you had a user named 'SAMPLE' and two tablespaces called 'SAMPLE' and 'SAMPLE_INDEX', then you'd need to do the following:

DROP USER SAMPLE CASCADE;
ALTER TABLESPACE SAMPLE OFFLINE;
DROP TABLESPACE SAMPLE INCLUDING CONTENTS;
ALTER TABLESPACE SAMPLE_INDEX OFFLINE;
DROP TABLESPACE SAMPLE_INDEX INCLUDING CONTENTS;

Answer 2

DROP USER---->
DROP USER USER_NAME CASCADE;
DROP TABLESPACE---->
DROP TABLESPACE TABLESPACE_NAME INCLUDING CONTENTS AND DATAFILES;