Oracle 11gR2 – How to Drop and Remove Datafiles

datafileoracleoracle-11goracle-11g-r2

We have a database of 2000 users.

We will drop all remaining schemas, leaving only 2 end users, excluding Internal (Oracle maintained) users.

After this operation, there will be a very high reduction in database size.
And we want to delete all unused datafiles first from the database and then physically from the server.

Is it possible to process it? How can we plan this operation? Can you help with exact commands?

Kind regards,

Best Answer

You can identify the tablespaces that are empty with this:

select tablespace_name
from dba_tablespaces
where tablespace_name not in (select tablespace_name
                              from dba_segments)
;

And with that list, drop the selected tablespaces with

drop tablespace <tablespace name> 
including contents
and data files;
Related Question