Oracle Tablespace Local Management: How to Create and Choose

Introduction (100–160 chars)
In oracle 19c, creating a tablespace with local management automates extent management and stabilizes operations. This article clarifies “AUTOALLOCATE vs. UNIFORM” and whether to add ASSM, with steps and runnable examples for beginner to intermediate readers.

Conclusion & Fast Path (to-do list)
Background & Basics
Procedure & Implementation (non-CDB/19c, Linux)
Troubleshooting (observe first → then remediate)
Operations, Monitoring, and Security Notes
Frequently Asked Questions (FAQ)
Summary (key points)

Conclusion & Fast Path (to-do list)

Decide your goal (mixed workloads vs. strict control).
Create a permanent tablespace.
- Leave sizing to the database → EXTENT MANAGEMENT LOCAL AUTOALLOCATE (recommended).
- Strict, uniform extent size → ... LOCAL UNIFORM SIZE 1M, etc.
Add ASSM if possible → SEGMENT SPACE MANAGEMENT AUTO (cannot be changed later).
Create temporary tablespace with UNIFORM (e.g., 1M) (AUTOALLOCATE not allowed).
Create a user, set default tablespace/quota, and verify.

Background & Basics

What is “local management”?

A mode where bitmaps inside each data file manage extents (allocation units). It reduces dictionary dependency and allows fast, concurrent allocate/free operations.

Key benefits

Faster space ops with better concurrency; lower dependency on dictionary tables
With AUTOALLOCATE, the DB auto-chooses suitable extent sizes (simpler ops)
No need to coalesce free space
Temporary tablespaces under local management avoid generating UNDO/REDO (also friendly to physical standby).

AUTOALLOCATE vs. UNIFORM (which should I choose?)

┌──── When mixed-size objects coexist ────┐
│   AUTOALLOCATE (default): DB auto-optimizes │
│   extent growth size. Easier operations.     │
│   (Allows minor space overhead.)            │
└─────────────────────────────────────────────┘
┌──── When you want strict sizing control ────┐
│   UNIFORM SIZE <N>: All extents have        │
│   identical size. Easier capacity planning. │
└─────────────────────────────────────────────┘

AUTOALLOCATE typically starts around 64KB (depends on block size), while UNIFORM defaults to 1MB if omitted. Choose per use case.

Segment Space Management (ASSM)

With SEGMENT SPACE MANAGEMENT AUTO, bitmaps manage free space in blocks after row DML (generally more efficient than freelists of MSSM). You cannot change this after creation. Not allowed in the SYSTEM tablespace.

Procedure & Implementation (non-CDB/19c, Linux)

Assumptions

OS: Oracle Linux (equivalent)
DB: Oracle Database 19c (non-CDB)
Privilege: SYSDBA
File layout example: /u01/app/oracle/oradata/ORCL/
Impact/Rollback: Creating a new tablespace does not affect existing objects. If not needed, you can drop it with DROP TABLESPACE ... INCLUDING CONTENTS AND DATAFILES (be careful not to delete incorrectly; take backups beforehand).

1) Create permanent tablespace (choose AUTOALLOCATE or UNIFORM)

AUTOALLOCATE (recommended, general purpose) example:

The database determines extent sizes automatically.

CREATE TABLESPACE ts_app
  DATAFILE '/u01/app/oracle/oradata/ORCL/ts_app01.dbf' SIZE 200M
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  SEGMENT SPACE MANAGEMENT AUTO;

Point: Simplifies operations; good for mixed workloads.

UNIFORM (strict control) example:

Forces all extents to be 1MB.

CREATE TABLESPACE ts_app_u1m
  DATAFILE '/u01/app/oracle/oradata/ORCL/ts_app_u1m01.dbf' SIZE 512M
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
  SEGMENT SPACE MANAGEMENT AUTO;

Point: Predictable capacity planning; helps avoid fine-grained fragmentation.

Note: When you specify EXTENT MANAGEMENT LOCAL, legacy storage clauses such as DEFAULT STORAGE/MINIMUM EXTENT are ignored or incompatible under certain conditions. See the SQL Reference for details.

2) Create temporary tablespace (UNIFORM recommended)

AUTOALLOCATE is not allowed for temporary tablespaces. UNIFORM with 1MB is recommended.

CREATE TEMPORARY TABLESPACE temp1m
  TEMPFILE '/u01/app/oracle/oradata/ORCL/temp1m01.dbf' SIZE 4G
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Reason: UNIFORM 1MB is generally recommended for temp tablespace performance/operational stability.

3) Set default temporary tablespace / create user and assign

First, set the default temporary tablespace.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp1m;

Create an application user; set default tablespace, temp tablespace, quota, and grant minimum privileges (do not use @ in passwords).

-- Create user
CREATE USER ts_demo IDENTIFIED BY "DemoPass1!";
-- Default and temporary tablespaces
ALTER USER ts_demo DEFAULT TABLESPACE ts_app TEMPORARY TABLESPACE temp1m;
-- Quota (adjust as needed)
ALTER USER ts_demo QUOTA 200M ON ts_app;
-- Minimal privileges
GRANT CREATE SESSION, CREATE TABLE TO ts_demo;

Intent: DEFAULT TABLESPACE pins the storage location; QUOTA constrains usable space.

4) Verify behavior (create a table and observe growth)

Create a sample table and grow data to observe extent allocation behavior.

CONN ts_demo/"DemoPass1!";
CREATE TABLE t_bulk (id NUMBER, pad CHAR(1000));

-- Insert 100k rows (adjust size per environment)
BEGIN
  FOR i IN 1..100000 LOOP
    INSERT INTO t_bulk VALUES (i, RPAD('x',1000,'x'));
    IF MOD(i,1000)=0 THEN COMMIT; END IF;
  END LOOP;
END;
/

How to see results: Check BYTES growth in USER_SEGMENTS. AUTOALLOCATE expands in staged sizes; UNIFORM grows at a fixed pitch.

5) Monitoring/visibility queries (copy-paste ready)

List tablespace modes and allocation types (AUTOALLOCATE/UNIFORM, ASSM).

COL tablespace_name FOR a20
COL extent_management FOR a10
COL allocation_type   FOR a12
COL segment_space_management FOR a6
SELECT tablespace_name, extent_management, allocation_type, segment_space_management
  FROM dba_tablespaces
  ORDER BY tablespace_name;

Check sizes and autoextend settings for data/temp files.

COL file_name FOR a60
SELECT file_name, tablespace_name, bytes/1024/1024 MB, autoextensible, maxbytes/1024/1024 MAX_MB
  FROM dba_data_files
  ORDER BY tablespace_name, file_name;

SELECT file_name, tablespace_name, bytes/1024/1024 MB, autoextensible, maxbytes/1024/1024 MAX_MB
  FROM dba_temp_files
  ORDER BY tablespace_name, file_name;

Check segment usage.

COL segment_name FOR a30
SELECT segment_name, segment_type, tablespace_name, bytes/1024/1024 MB
  FROM user_segments
  ORDER BY bytes DESC FETCH FIRST 10 ROWS ONLY;

Troubleshooting (observe first → then remediate)

Symptom (representative ORA)	Main cause	First checks (read-only)	Typical remediation (change; at your own risk)
ORA-01658: cannot create INITIAL extent	Not enough contiguous free space	`AUTOEXTENSIBLE/MAXBYTES` in `DBA_DATA_FILES`, free space fragmentation	Add/extend data files; if UNIFORM, revisit size.
ORA-01659: unable to allocate beyond MINEXTENTS	Mismatch between initial/next extent sizing and free space	Same as left + object `MINEXTENTS/NEXT`	Add/extend data files; consider reducing `MINEXTENTS/NEXT` via object rebuild.
ORA-03206: MAXSIZE for AUTOEXTEND exceeded	Block-size-dependent limit exceeded	Block size; configured `MAXSIZE`	Adjust `MAXSIZE` within permitted range (e.g., around the 32GB threshold ±1MB).
Want to change ASSM later	Not changeable	`DBA_TABLESPACES.segment_space_management`	Create a new tablespace and move objects (`ALTER TABLE ... MOVE`, etc.).
Want AUTOALLOCATE for temp tablespace	Not allowed	SQL Reference	Recreate with UNIFORM (e.g., 1MB).

Operations, Monitoring, and Security Notes

Capacity management: Always set AUTOEXTEND ON MAXSIZE and alert on thresholds.
Default TS / quota: App users should have only their app tablespace as default with appropriate quota (prevents misplacement/bloat).
ASSM consistency: Avoid mixing ASSM modes for the same functional category.
Temp TS monitoring: Track peak usage; keep UNIFORM 1MB with a reasonable number of temp files.
Rollback approach: For mistakes, safest is create new → move objects → drop old (ASSM and UNIFORM size are immutable after creation).

Frequently Asked Questions (FAQ)

Q1. Can I switch an existing tablespace to ASSM (AUTO) later?
A. No. It is fixed at creation time. Create a new tablespace and move tables/indexes as needed.

Q2. Can I use AUTOALLOCATE for a temporary tablespace?
A. No. Use UNIFORM (1MB recommended).

Q3. I’m unsure between AUTOALLOCATE and UNIFORM.
A. Choose AUTOALLOCATE for general purposes. If you need strict, predictable capacity planning or visual regularity, choose UNIFORM.

Q4. How often should I review capacity?
A. Depends on change rate, but at least daily for DBA_DATA_FILES/DBA_TEMP_FILES usage and remaining headroom.

Summary (key points)

For permanent tablespaces, choose AUTOALLOCATE (recommended) or UNIFORM per use case.
Adding ASSM (AUTO) at creation simplifies operations in many cases (not changeable later).
Temporary tablespaces should use UNIFORM (e.g., 1MB) (AUTOALLOCATE not allowed).
Monitor both data/temp file sizes together with autoextend; for errors, isolate “capacity,” “contiguous free space,” and “limits.”

This article targets Oracle Database 19c (screens and defaults may differ in other versions).

[reference]
Managing Tablespaces