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,

  • I'd create a new database, export the 2 users from the old, import them in the new, drop everything (carefully) from the old database. Commented Sep 20, 2021 at 12:43
  • @GerardH.Pille - and then what? Identify every client, external job, etc. and redirect it to the new database?
    – EdStevens
    Commented Sep 20, 2021 at 14:41
  • It would be easier, once the old one has been removed, to rename the new one and let it identify itself as the old one. Clients need never know. Commented Sep 20, 2021 at 15:12
  • Be sure to have a tested backup before you start this exercise. Do you know how to "test" a backup? Commented Sep 20, 2021 at 19:49

1 Answer 1


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;
  • But the tablespaces won't be empty, will they? Unless those two schemas use separate tablespaces. Commented Sep 20, 2021 at 15:14
  • @GerardH.Pille It's actually a very good method if specific tablespaces are created for each group of users. But you are right, how can we improve this method if different schemas are created under the same tablespace? I have this in mind; first, the method that EdStevens specified is applied. Then we move some users under the tablespace to be dropped and everything owner it to another tablespace. Then we drop the other tablespace. Is it possible?
    – jrdba123
    Commented Sep 20, 2021 at 16:29
  • Rebuilding each table and index belonging to the 2 users, in new tablespaces, is certainly feasible. Commented Sep 20, 2021 at 16:52
  • @GerardH.Pille - good point re: the TS still having segments from the remaining users/schemas. Should be noted that the OP originally asked about dropping 'data files', but of course the files cannot be considered apart from the TS they implement. If a TS has multiple data files, I'm not sure you can drop them individually. So as said, at that point it would be best to rebuild the remaining segments into a new TS and completely drop the old.
    – EdStevens
    Commented Sep 20, 2021 at 17:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.