When I give the command to drop a user i.e. DROP USER 'username' cascade,
Does it deletes all the tablespace and datafiles used by that particular user.
If not, what is the command to delete all the tablespace / datafiles / disk space that were used by that particular user.
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;