Huge Increase in Storage Needed to Store Data in Oracle

Question

I have a table stored in a tablespace where the size growth is usually not too big. However, on the last data loading, it suddenly needed so much more additional space, while inserting the same exact data. Here's a history of the tablespace size

SELECT * FROM bcadw.tablespace_monitoring
WHERE tablespace_name = 'TESTING'  AND last_update_dt >= trunc(SYSDATE - 97);

On Jan 5th I inserted some data into the table, and since there's duplicate data from the loading, on Jan 6th I truncated the table and reloaded the table (with less row count). However, the table suddenly needed a huge increase of space in order to store the data. What may be the cause of this problem?

Answer 1

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:

1. Identify tablespaces with free space
2. 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).