Understanding Oracle Tablespace Management: Auto vs Manual

Segment size management manual vs segment size management auto:
AUTOMATIC Segment Space management in tablespace used bitmap for tracking the freelist, it remove use of freelist management which cause the contention on the segment header that occurs with freelists management.
MANUAL used freelist (like linked list concept) for managing the space.

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

Check tablespace is automatic or manual

SELECT tablespace_name, segment_space_management FROM dba_tablespaces;

TABLESPACE_NAME                SEGMEN
------------------------------ ------
SYSTEM                         MANUAL
SYSAUX                         AUTO
UNDOTBS1                       MANUAL
TEMP                           MANUAL
USERS                          AUTO

Create Space Management in tablespace

AUTOMATIC

CREATE TABLESPACE salestb DATAFILE 'D:\oracle\oradata\orcl\sales_001.dbf' SIZE 100M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

MANUAL

CREATE TABLESPACE salestb DATAFILE 'D:\oracle\oradata\orcl\sales_001.dbf' SIZE 100M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT MANUAL;


Note: Create your tablespaces with the locally managed and automatic segment space management features (ASSM) enabled.
(default behavior) for Better Performance also.

Default create behavior by Oracle

-- Create a tablespace with name sales_db
create tablespace sales_db datafile 'D:\oracle\oradata\orcl\sales_01.dbf' size 100m;

-- Check the default configuration Oracle Choose:
select tablespace_name, extent_management, segment_space_management from dba_tablespaces where tablespace_name='TOOLS';

TABLESPACE_NAME EXTENT_MANAGEMENT SEGMENT_SPACE_MANAGEMENT
--------------- ----------------- -------------------------
SALES_DB        LOCAL             AUTO

Modify or switch between automatic or Manual space management

Note: There is no way to convert the tablespace from automatic to manually or vise versa.

1. Simple method is create a new tablespace with desired options and move the objects from the old tablespace to new tablespace.
Option 1:
Use index or tablespace movement commands:

-- For table
ALTER TABLE table_name MOVE new_tablespace;

-- For Index
ALTER INDEX index_name REBUILD new_tablespace;

With following query you find the objects present in your tablespace and commands of movement:
Note: Specify the tablespace name from which you choose to move means source tablespace.

SELECT Segment_Name, Segment_Type, Tablespace_Name, Bytes, Blocks, Extents, q'[ALTER ]' || Segment_Type || q'[ ]' || USER || q'[.]' || Segment_Name || CASE WHEN Segment_Type = 'INDEX' THEN q'[ REBUILD ]' ELSE q'[ MOVE ]' END || q'[ TABLESPACE ]' || q'[TABLESPACE_NAME_GOES_HERE]' || q'[;]'
FROM Dba_Segments WHERE Tablespace_Name = 'USERS' ORDER BY Segment_Name;


Option 2:
Use Export or import utility to move the data from one tablespace to other tablespace.