Tablespace, not fragmented any more (oracle 9ir2 onwards)
Since oracle 9ir2 onwards, all of tablespaces are created as locally-managed tablespace as default. Comparing to dictionary-managed tablespace,locally-managed tablespaces track all extent information in the tablespace itself, using bitmaps, resulting in the following benefits:
- Improved concurrency and speed of space operations, because space allocations and deallocations predominantly modify locally managed resources (bitmaps stored in header files) rather than requiring centrally managed resources such as enqueues
- Improved performance, because recursive operations that are sometimes required during dictionary-managed space allocation are eliminated\
- Reduced the need to coalesce tablespace and also avoided usage of system undo segment
- Readable standby databases are allowed, because locally managed temporary tablespaces (used, for example, for sorts) are locally managed and thus do not generate any undo or redo.
- Simplified space allocation–when the
autoallocateclause is specified, appropriate extent size is automatically selected- Reduced user reliance on the data dictionary because necessary information is stored in file headers and bitmap blocks
In a tablespace where extents are locally managed, free extents are stored in a bitmap in
the tablespace. Each bit in the bitmap represents a single database block or a multiple
of database blocks if extent management has been configured to be of a uniform size
or automatic. As an extent is allocated to a segment or freed up because a segment was
dropped, truncated, or resized, the bitmap is updated to reflect the change.
The point is if we create a locally-managed tablespace with option “uniform size”, we don’t need to worry about tablespace fragment any more. That’s a truth onward oracle 9ir2.
If, you are using oracle of lower version or you create dictionary-managed tablespace on special purpose, you can use the following script to detect if your tablespaces are fragmented.
script to measures the fragmentation of free space on dictionary-managed tablespace
Popularity: 2% [?]
Tags:none
