Let us we discuss about ASSM (Automatic Space Segment Management) in tablespace
What is segments ?
- Segments is a collection of extents that contain all the data for a specific logical storage structure within a tablespace .
- Segment Space Management is allow to specif y how free and used space within a segment is to be managed
Types of segment space management
- Oracle that you want to use free lists for managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows. This form of managing space within segments is called manual segment-space management.
- We need to specify and tune the
FREELISTS GROUPS storage parameters for schema objects created in the tablespace.
- Oracle that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows.
- As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management.
- Automatic segment-space management delivers better space utilization than manual segment-space management, and it is self tuning in that it scales with increasing the number of users, as well as instances.
SQL> CREATE TABLESPACE lmtbsb DATAFILE '/u01/app/oracle/product/18.104.22.168/oradata/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
( Note: The
FREELIST GROUPS parameters are ignored in this type of tablespace.)
To view the segment space Auto or manual
SQL> select SEGMENT_SPACE_MANAGEMENT from dba_tablespaces; or SQL> select SEGMENT_SPACE_MANAGEMENT from user_tablespaces;
Note : By default is manual