In a locally managed tablespace, there are two methods that Oracle Database can use to manage segment space:
To manage free space in the segment:
Automatic segment space management is the more efficient method, and is the default for all new permanent, locally managed tablespaces.
Automatic segment space management is the default for all new permanent, locally managed tablespaces.
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO; -- ASSM is enabled
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT MANUAL; -- ASSM is disbaled
You cannot change the segment space management mode of a tablespace.
select tablespace_name, segment_space_management from dba_tablespaces;
TABLESPACE_NAME | SEGMENT_SPACE_MANAGEMENT |
---|---|
SYSTEM | MANUAL |
SYSAUX | AUTO |
UNDOTBS | MANUAL |
TEMP | MANUAL |
USERS | AUTO |