I have a tablespace called DATA, and it is setup with auto-extend as false. This tablespace has two datafiles and is set up so it takes up 350 GB of physical space.
A week ago I queried user_tablespaces and dba_data_files and noticed it had 20% of available logical space. I then procedeed with a cleanup and removed many records from tables in this tablespace. We were expecting to see a big increase in available space. Unfortunately when I queried the views I noticed the available space was now 20.5%.
Could this be due to data fragmentation? Can we "defrag" the tablespace somehow and recover the lost space? Or do we need to recreate the tablespace from scratch?