I am working with my BI team on their nightly data migration. There have been numerous issues with their incremental replication and merge approach. It was both slow and unreliable, as there was no trustworthy “last updated” field on their dataset.
Consequently, I recommended we test a truncate/reload approach. With a few exceptions, the individual tables are KB or MB in terms of space-on-disk. As a result, I expected no issues on an enterprise class system. Yet, we are repeatedly running out of TEMP disk space.
It was my impression that TRUNCATE TABLE PEOPLE DROP STORAGE was designed to instruct the server to “clear” the table and make the space immediately available for overwrite. It also should result in negligible logging, as there is no rollback requirement.
In contrast a DELETE FROM PEOPLE command incurs logging to facilitate rollback and is not generally “immediately” available for overwrite. Some systems have a separate process to “scavenge” this disk space in the background. I am not sure about Oracle.
Any thoughts on why our TEMP space is filling up during a truncate?
Any thoughts on how to get the database to “immediately” reclaim the space that just got truncated?