![oracle 10g tablespace fragmentation oracle 10g tablespace fragmentation](https://blogs.sap.com/wp-content/uploads/2007/12/copyofimage001_51550.gif)
Create a table in each tablespace (with the same structure and data).Create two tablespaces, one (tbs_uni) will use uniform extent allocation of 1MB, the other one (tbs_sys) will use system extent allocation.In order to do this, we will perform the following: Now we’ll try to understand how much space is released back to the tablespace. We saw the idea of shrink table and we understand that it is a very useful command. How Much Space does Shrink Table Release?
![oracle 10g tablespace fragmentation oracle 10g tablespace fragmentation](https://docs.oracle.com/cd/B14156_01/doc/B13788/html/archovw.gif)
#ORACLE 10G TABLESPACE FRAGMENTATION FREE#
Once the HWM has been moved, the blocks beyond it are considered as free blocks (third drawing) and can then be released from the table back to the tablespace (last drawing) Locally Managed Tablespaces Then, Oracle can move the HWM to the last row of the table, which is now not at the end of the table (second drawing). The shrink table command will move the rows from the end of the table into free places closer to the beginning of the table (first drawing). Assuming we deleted many rows, there are now many places in the table blocks to contain new rows. In the diagram below we see that the HWM is at the end of the table. Once there are no free blocks left, another extent is allocated. When rows are inserted into table and there is no space in the “used block”, Oracle moves the HWM (the black line) towards the end of the table, marking the “empty blocks” as “used”. Then, the blocks after the HWM can be released and the table size is reduced.Īs you can see below, in normal operation, a table has “used blocks” (the blue one) and “empty blocks” (the orange ones). After moving the rows, a table lock takes place while Oracle moves the HWM backwards. How does it work? When we perform the shrink command, Oracle uses row movement (which must be enabled on the table using “alter table … enable row movement”) to move rows from the last blocks of the table to the beginning of the table. In 10gR1, Oracle introduced an exciting feature called “shrink table”. When we deleted many rows from the table, the HWM and the table size remained and the only way to decrease its size was by truncating the table. Until Oracle 10gR1, when the High Water Mark (HWM) of the table moved forward (as new rows were inserted into the table), it could not be moved backwards in order to decrease the table size and de-allocate space, releasing it back to the tablespace free space. There can be many causes of tablespace fragmentation, however, I never thought that a frequent “shrink table” command would cause such a fragmentation so quickly.
![oracle 10g tablespace fragmentation oracle 10g tablespace fragmentation](https://www.oracletutorial.com/wp-content/uploads/2015/12/oracle-instance.jpg)
In this article I will discuss tablespace fragmentation, which causes a waste of space. It can appear in many ways and components and can cause all kind of problems. This is an article that was published in the past, however, it’s not available anymore so I decided to post it here.įragmentation is a common issue which we try to avoid whenever possible.