Prevent fragmentation in tablespace is most crucial part in database administration. It degrades performance and wastes disk space. Article explains how fragmentation occurs in dictionary managed tablespace in Oracle database. |
Tablespace fragmentation is not unusual in most data source, and to a certain level, it is almost unavoidable, unless particular actions are taken to avoid it right from plenty of duration of preliminary data source settings and section design. We will take a look at tablespace fragmentation to comprehend the concerns engaged and some protective and ameliorative actions necessary to get over it.
Desk fragmentation (as well as table fragmentation) can be confirmed either as a slice of continuous place (referred to as fizz or bubble) or as a sequence of items which are close to each other (known as a variety of fizz or honeycomb). Thus, a percolate consists of one or more prevents to be registered, while a honeycomb consists of several pockets. A honeycomb can be registered to form a huge percolate key. Bubbles between following plug-ins allocated to the items saved in that tablespace. A little percolate, which consists of one or a few prevents, can result in spend of place if it is incapable to evolve to the new plug-ins of the items within the tablespace. In case of nappies, they consist of a lot of pockets or even a few huge pockets, and then it is likely misused place is little, since the honeycomb will be able to provide new proportion evaluate. However, little sections made up of two or three prevents pockets, which are quite common, are more likely to generate space-wasteful, unless the styles of expansion of the items that need to grow are little enough to fit within them.
Both bubbles and combs are the remnants of far-allocations within tables. When a table is dropped or truncated, the extents, if previously occupied are released. Other reasons for the extension to be released include the use of SQL command to release. Furthermore, in the case of rollback segments or undo segments, extensions can be released when the segment shrinks. If the extensions are released and adjacent each other form a honeycomb. Otherwise, f that are scattered throughout the table space, each remains effective as a bubble. Also, if some of these extensions of-allocated next to the pre-existing bubbles, then that together form a honeycomb, with the larger bubbles. Note that it is in the case of honeycomb, by default, the edge around each bubble remains intact, separating them from each other. However, it is possible to pick them up to bring the edges down and form a big bubble next. Also, if there are other neighbors in the free space chunks, which can be part of this great party. Whenever a new extension should be assigned to a segment and the size of the new extension is less than or equal to the largest available free space bubble, the measure can be assigned to that bubble. If the pieces released to drop the table are not adjacent to each other and there is no neighboring pieces of free space, each point remains an independent spirit and a new extension can use the bubble only if the new extensions specific size of the bubble.
Author is having 15+ years experience and providing Database Support and Online Oracle DBA Courses.
Related Articles -
Tablespace fragmentation, dictionary managed tablespace, oracle tablespace, segment management, oracle space management, databases,