Oracle Segment - (Free) Space Management (ASSM) of a Tablespace

About

In a locally managed tablespace, there are two methods that Oracle Database can use to manage segment space:

  • automatic
  • and manual.

To manage free space in the segment:

  • Manual segment space management uses linked lists called “freelists”
  • Automatic segment space management uses bitmaps.

Automatic segment space management is the more efficient method, and is the default for all new permanent, locally managed tablespaces.

Maintenance

Default

Automatic segment space management is the default for all new permanent, locally managed tablespaces.

Create

  • Automatic (ASSM)
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
    EXTENT MANAGEMENT LOCAL 
    SEGMENT SPACE MANAGEMENT AUTO; -- ASSM is enabled
  • Manual
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
    EXTENT MANAGEMENT LOCAL 
    SEGMENT SPACE MANAGEMENT MANUAL; -- ASSM is disbaled

Modify

You cannot change the segment space management mode of a tablespace.

View

select tablespace_name, segment_space_management from dba_tablespaces;
TABLESPACE_NAME SEGMENT_SPACE_MANAGEMENT
SYSTEM MANUAL
SYSAUX AUTO
UNDOTBS MANUAL
TEMP MANUAL
USERS AUTO

Documentation / Reference


Powered by ComboStrap