Monday, March 14, 2011

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.
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 tablesapce 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.

and x$table list below :

http://www.orafaq.com/wiki/X%24_Table

No comments: