Oracle reclaim disk space after drop user cascade

Question

I am using Oracle Database 12c. I had a big schema which was taking more than 20GB of my disk space. So I dropped it using the command:

 drop user myDB cascade;

However, the tablespace files continue to take space from the disk. In the image bellow taken from EM Express, we can see it has a lot of free space in between the data I still need.

My question is: how do I reorganize my tablespace to be in just one file and then have it resized so that I can have some more space in Disk?

Thanks in advance.

Answer 1

This can be done by first creating a new tablespace with 1 datafile and then moving all segments from old tablespace to new tablespace, after all data is moved you can drop the old tablespace and rename new to old. In this way the new tablespace is compact and you free up unused disk space so that is available to the OS.

Sequence of steps :

1. create new tablespace TBS_B with 1 datafile
2. find out all the segments that need to be transferred from TBS_A to TBS_B using"dba_segments" view

3. transfer table segments via "alter table ... move tablespace" command

   transfer index segments via "alter index ... rebuild tablespace" command

4. Drop old tablepsace TBS_A
5. Rename new tablespace TBS_B to TBS_A

Answer 2

One thing to note here is that dropping a user in oracle will not release space from its tablespace. A tablespace takes its own space and it acts as a virtual storage space for database users. So dropping a user will create free space in that tablespace, which can then be used by another user of that tablespace.

To check the available Free space in all tablespaces use this query:

select tot.tablespace_name, tot.file_name, tot.bytes/1024/1024 size_mb, free.free_mb 
from dba_data_files tot, (select file_id, sum(bytes/1024/1024) free_mb FROM dba_free_space free group by file_id) free
where tot.file_id=free.file_id
order by free.free_mb;

For reorganizing a tablespace, you will have to reorganize its contents ie, tables and indexes. The reorganizing will need to have double the space in your tablespace, as it will move tables and indexes into new blocks, but after reorganize, you can reclaim the space by resizing the tablespace datafiles.

To Move tables and indexes, first determine which objects are using your tablespace using dba_segments view and then move/rebuild those objects using these commands:

alter table TABLENAME move;
alter index INDEXNAME rebuild online;