0

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,

Improve this question
4
  • 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.
    – Gerard H. Pille
    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
    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.
    – Gerard H. Pille
    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?
    – Gerard H. Pille
    Sep 20, 2021 at 19:49

1 Answer 1

Reset to default
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;
Improve this answer
4
  • But the tablespaces won't be empty, will they? Unless those two schemas use separate tablespaces.
    – Gerard H. Pille
    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
    Sep 20, 2021 at 16:29
  • Rebuilding each table and index belonging to the 2 users, in new tablespaces, is certainly feasible.
    – Gerard H. Pille
    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
    Sep 20, 2021 at 17:06

Your Answer

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

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