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

oracle10g
Sanjay Thakur picture Sanjay Thakur · Jun 9, 2009 · Viewed 93.4k times · Source

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

Sliff picture Sliff · Jun 9, 2009

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;

Related questions

How to find the privileges and roles granted to a user in Oracle?

I am using Linux, Oracle10g. I have created one user called test. and granted create session and select any dictionary permission to the same user. i also granted sysdba and sysoper roles to the same users. Now i want …

Read More
Checking oracle sid and database name

I want to check SID and current database name. I am using following query for checking oracle SID select instance from v$thread; but table or view does not exist error is coming. I am using following query for checking …

Read More
CASE .. WHEN expression in Oracle SQL

I have the table with 1 column and has following data Status a1 i t a2 a3 I want to display the following result in my select query Status| STATUSTEXT a1 | Active i | Inactive t | Terminated a2 | Active a3 | Active One …

Read More