ASSM

From Oracle FAQ
Jump to: navigation, search

ASSM (Automatic Segment Space Management) is a method used by Oracle to manage space inside data blocks. It eliminates the need to specify parameters like PCTUSED, Freelists and Freelist groups for objects created in the tablespace.

History[edit]

ASSM was first introduced with Oracle 9i. Starting with 10g Release 2, ASSM will be enabled by default when you create a new tablespace.

Free space tracking[edit]

Instead of trying to track the exact space inside a block (with a freelist), Oracle marks the block as one of six types, tracked by a bitmap. The 6 different "freeness statuses" are:

  • 0 = unformatted
  • 1 = logically full
  • 2 = 0-25% free
  • 3 = 25-50% free
  • 4 = 50%-75% free
  • 5 = 75-100% free

Example[edit]

Create an ASSM managed tablespace by specifying "SEGMENT SPACE MANAGEMENT AUTO":

CREATE TABLESPACE ts1 
DATAFILE '/app/orafata/ts1.dbf' SIZE 10M
EXTENT MANAGEMENT LOCAL       -- Enable LMT
SEGMENT SPACE MANAGEMENT AUTO -- Enable ASSM
/

Test[edit]

Here is a simple test to see if a tablespace is freelist or ASSM managed:

SQL> SELECT tablespace_name, extent_management, segment_space_management
  2    FROM dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN SEGMEN
------------------------------ ---------- ------
SYSTEM                         LOCAL      MANUAL
SYSAUX                         LOCAL      AUTO
UNDOTBS1                       LOCAL      MANUAL
TEMP                           LOCAL      MANUAL
USERS                          LOCAL      AUTO
EXAMPLE                        LOCAL      AUTO

6 rows selected.

Also see[edit]

  • Freelist - Manual segment space management
  • LMT - Locally Managed Tablespaces
Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #