Oracle pretty much never reduces the size of a file automatically: truncating a table doesn't actually shrink the size of a tablespace or the datafiles in the tablespace, or even the amount of space allocated to the table. It only releases rows of the specific table so that space can be reused unless you include the
DROP STORAGE option.
TRUNCATE TABLE table_a DROP STORAGE;
Note that if you don't drop the storage, a select of the table will scan all of the empty blocks as well as any blocks containing new data. If you want to reclaim the space from the actual tablespace data files, you must also perform several other steps, as outlined in this article: Reclaiming Unused Space in Datafiles
In a nutshell:
- Identify tablespaces with free space
- Reorganize the tablespace (several methods are possible)
Tablespace reorganization usually means relocating objects out of the tablespace - either exporting to an external file or relocating to a separate tablespace - so that the original tablespace data files can be shrunk, then moving or importing the objects back to their original location. It is a relatively simple process, but usually involves downtime.
Also note that the tablespace may contain a number of things other than your table data as well, such as indexes or LOB segments, that require separate handling such as rebuilding or relocation.
If your tablespace contains only a single table and no other objects, then a
ALTER TABLE ... SHRINK SPACE command as described here might be enough to help lower the high water mark within the file and allow for an
ALTER DATABASE DATAFILE ... RESIZE command to work, though it doesn't move all data blocks closer to the front of the file(s).