Say, you create a table t. From time to time, you delete all the data in t and insert fresh records again. If that table grows to 1GB before deleting all the data, where are the 1GB after I delete all the rows in t? What's the difference between the two SQL statements, DELETE FROM t vs. TRUNCATE TABLE t, in term of space usage. Yes, the 1GB are still there, available for subsequent use, but in Oracle, there is some subtlety you must be aware of.
In Oracle, we store data in tables, while Oracle stores objects, including tables, in a tablespace. The space is allocated on demand. If the space allocated to a table is not enough, Oracle requests data blocks from the tablespace to accomodate new data. If the space in the tablespace is not enough, it grows to the next extent.
Oracle maintains a counter, High Water Mark(HWM), in the table indicating how many blocks have been allocated to the table. When the size of a table increases, the counter increases. When records are deleted and some data blocks are empty, the space is returned back to the table and the counter remains unchanged. Under manual segment space management, which is an attribute of a tablespace, the pointer to the free data blocks is appended to a FREELIST structure in the table. On the other hand, under automatic segment space management, a bitmap in the header of the data block is reset. So space allocated to a table remains in the table, whether it's in use or empty.
However, if you delete all the data in a table by issuing the command TRUNCATE t, Oracle returns the space back to the tablespace, which can be used by other tables, not just t and the HWM of the table is reset to zero. There are other uses of HWM, but here, I only care about its relationship with space. After Oracle 10g, you can also issue ALTER TABLE t SHRINK SPACE command to release the unused space back to the tablespace after DELETE FROM t.
Back to our question, running DELETE FROM t command without shrinking space, the 1GB are available for t only, while running TRUNCATE TABLE t command, the 1GB are returned back to the tablespace, available for all objects. So it's possible your tablespace keeps growing even if you have deleted data in tables.
Further Reading:Using TRUNCATE
1 comment:
I think you all know how to delete table in Oracle but some of you might not aware of how to truncate table so for this purpose this post is written. Both commands are given in this post. You can apply them and see the difference between the two commands. This is perfect work.
sap project
Post a Comment