ASSM

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

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

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

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

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

• 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	#