Creating Initial Tablespaces in Oracle AI Database 26ai

To create an initial tablespace in Oracle AI Database 26ai — especially when creating a custom database by hand, or following Optimal Flexible Architecture (OFA) guidelines^[1] — you must define at least:

1. The tablespace name
2. The datafile location and size
   • How the space is managed (locally managed extents)
   • Whether autoextension is enabled

One more thing worth keeping in mind throughout this lesson: since Oracle Database 21c, every Oracle database is a container database (CDB) — there's no other supported option anymore. That matters for tablespaces specifically, because several of them (SYSTEM and SYSAUX in particular) aren't just created once for the whole database; they exist per container. More on that once we've covered the basics.

CREATE TABLESPACE users
  DATAFILE '/u01/app/oracle/oradata/COIN/users01.dbf' SIZE 100M
  AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE MANAGEMENT AUTO;

Clause	Purpose
DATAFILE	Specifies the physical location and initial size of the tablespace's datafile
AUTOEXTEND ON	Enables automatic growth when the file is full
NEXT 10M	Grows in 10 MB increments
MAXSIZE UNLIMITED	No upper limit on file size (can be controlled if needed)
EXTENT MANAGEMENT LOCAL	Uses a bitmap, stored in the tablespace itself, to track extents. This isn't just a best practice — dictionary-managed tablespaces (the alternative) are deprecated, and if you build a database manually with CREATE DATABASE and don't specify this clause, the SYSTEM tablespace silently defaults to the deprecated dictionary-managed form.
SEGMENT SPACE MANAGEMENT AUTO	Uses automatic free space management for better performance

1. Use locally managed tablespaces only (EXTENT MANAGEMENT LOCAL) — and include the clause explicitly if you're scripting CREATE DATABASE by hand, for the reason noted above.
2. Always set SEGMENT SPACE MANAGEMENT AUTO.
3. Store datafiles in OFA-compliant paths (e.g., /u01/app/oracle/oradata/DBNAME/...).
4. Use AUTOEXTEND with caution, and monitor disk space regularly — unlimited autoextend is convenient but can mask a runaway growth problem until the disk is full.
5. Keep UNDO and TEMP as separate, dedicated tablespaces during database creation.

While creating a full database, you'll also define these system tablespaces:

• SYSTEM – holds the data dictionary and compiled PL/SQL packages
• SYSAUX – auxiliary system tablespace (AWR, Enterprise Manager repository data, and more)
• UNDO – for undo segments, supporting transaction rollback and read consistency
• TEMP – for temporary operations such as sorts and hash joins

CREATE UNDO TABLESPACE undotbs1
  DATAFILE '/u01/app/oracle/oradata/COIN/undotbs01.dbf' SIZE 200M
  AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED;

CREATE TEMPORARY TABLESPACE temp
  TEMPFILE '/u01/app/oracle/oradata/COIN/temp01.dbf' SIZE 100M
  AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

Here's where the legacy version of this lesson needs more than a syntax refresh. A script that creates a database without enabling the pluggable database architecture no longer creates a valid Oracle AI Database at all — the multitenant container database (CDB) architecture has been the only supported option since Oracle Database 21c. The corrected script below adds the ENABLE PLUGGABLE DATABASE clause, which is what actually makes this a CDB: it creates the CDB root together with PDB$SEED, the system-supplied template every future PDB will be created from.

STARTUP NOMOUNT
  PFILE='/u01/app/oracle/product/26ai/dbs/initCOIN.ora';

CREATE DATABASE COIN
  USER SYS IDENTIFIED BY SysPassword#2026
  USER SYSTEM IDENTIFIED BY SystemPassword#2026
  LOGFILE GROUP 1 ('/u01/app/oracle/oradata/COIN/redo01.log') SIZE 100M,
          GROUP 2 ('/u01/app/oracle/oradata/COIN/redo02.log') SIZE 100M,
          GROUP 3 ('/u01/app/oracle/oradata/COIN/redo03.log') SIZE 100M
  MAXLOGFILES 5
  MAXLOGMEMBERS 5
  MAXLOGHISTORY 100
  MAXDATAFILES 100
  CHARACTER SET AL32UTF8
  NATIONAL CHARACTER SET AL16UTF16
  EXTENT MANAGEMENT LOCAL
  DATAFILE '/u01/app/oracle/oradata/COIN/system01.dbf' SIZE 700M
    AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
  SYSAUX DATAFILE '/u01/app/oracle/oradata/COIN/sysaux01.dbf' SIZE 550M
    AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
  DEFAULT TABLESPACE users
    DATAFILE '/u01/app/oracle/oradata/COIN/users01.dbf' SIZE 100M
    AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
  DEFAULT TEMPORARY TABLESPACE temp
    TEMPFILE '/u01/app/oracle/oradata/COIN/temp01.dbf' SIZE 100M
    AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
  UNDO TABLESPACE undotbs1
    DATAFILE '/u01/app/oracle/oradata/COIN/undotbs01.dbf' SIZE 200M
    AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
  ENABLE PLUGGABLE DATABASE
    SEED
    FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/COIN/',
                          '/u01/app/oracle/oradata/pdbseed/')
    SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
    SYSAUX DATAFILES SIZE 100M
  LOCAL UNDO ON;

Two things changed from a typical pre-21c script, both required, not optional:

• ENABLE PLUGGABLE DATABASE ... SEED FILE_NAME_CONVERT — without this clause (or one of its alternatives, Oracle Managed Files or the PDB_FILE_NAME_CONVERT initialization parameter), CREATE DATABASE doesn't build a CDB, and a non-CDB simply isn't a supported architecture anymore. The FILE_NAME_CONVERT pair tells Oracle how to derive the seed PDB's file paths from the CDB root's paths — here, replacing COIN/ with pdbseed/ in each generated filename. The ENABLE_PLUGGABLE_DATABASE initialization parameter must also be set to true in the pfile referenced by STARTUP NOMOUNT.
• LOCAL UNDO ON — this puts the CDB in local undo mode, meaning every PDB gets its own dedicated undo tablespace, created automatically. This is also DBCA's own default behavior when it builds a CDB for you, so setting it explicitly here just matches what you'd get from the graphical tool.

This is worth stating plainly, because the script above only tells half the story: in a CDB, every container — the CDB root and every PDB — needs, and by default automatically gets, its own SYSTEM and SYSAUX tablespace. The SYSTEM DATAFILES and SYSAUX DATAFILES clauses under ENABLE PLUGGABLE DATABASE SEED above are exactly this: they define the SYSTEM and SYSAUX tablespaces for PDB$SEED, separate from the CDB root's own SYSTEM and SYSAUX defined earlier in the same statement.

Practically, this means the single flat list of tablespaces you're used to thinking about from a non-CDB world — one SYSTEM, one SYSAUX, one UNDO, one TEMP for the whole database — only describes the CDB root. The moment you plug in or create your first application PDB, that PDB arrives with (or needs) its own SYSTEM and SYSAUX. Undo works a little differently: with LOCAL UNDO ON, each PDB automatically gets its own undo tablespace too, so you don't need to script that separately for each PDB the way you do for SYSTEM/SYSAUX.

After running CREATE DATABASE, a few housekeeping tasks remain:

• Build the data dictionary and PL/SQL packages. As covered in Lessons 7 and 8, the modern approach for a manually created CDB is a single top-level script rather than running the classic scripts one by one:

  @?/rdbms/admin/catcdb.sql

  This installs everything catalog.sql and catproc.sql used to handle separately, applied correctly to the CDB root.
• Configure a listener (optional) via NETCA, or manually in listener.ora.
• Create an spfile, if you haven't already:

  CREATE SPFILE FROM PFILE='/u01/app/oracle/product/26ai/dbs/initCOIN.ora';

One item from older versions of this checklist is deliberately absent here: running pupbld.sql to build the SQL*Plus Product User Profile. As covered in Lesson 9, PRODUCT_USER_PROFILE was desupported in Oracle Database 19c and shouldn't be relied on for security in 23c, 23ai, or 26ai — running it as a routine post-creation step would work against everything that lesson covers about granular privileges, RBAC, and Database Vault as the actual current recommendations.

Notes on this script:

• Adjust file sizes and paths for your own environment — the values here are reasonable starting points, not fixed requirements.
• Always validate your initCOIN.ora file before referencing it in STARTUP NOMOUNT.
• You can define additional tablespaces — TOOLS, or application-specific ones — after the database exists, the same way the USERS tablespace was created earlier in this lesson.

An Oracle database structured according to the Optimal Flexible Architecture (OFA) model includes a foundational set of tablespaces for modularity, scalability, and ease of maintenance:

1. USERS: Stores user-created schema objects — tables, indexes, views.
2. TEMP: Serves transient segments used for sorting operations and hash joins during SQL execution.
3. UNDO: Maintains undo data required for transaction consistency, rollback, and flashback features.
4. TOOLS: Accommodates metadata and objects for optional Oracle tools such as Enterprise Manager or application monitoring utilities.

One more worth knowing about, not part of the classic OFA list but a real current option: Oracle AI Database tablespaces are either smallfile (the default — a tablespace can span multiple, individually smaller datafiles) or bigfile (a single very large datafile per tablespace, which simplifies file management when paired with Oracle Managed Files or ASM). Nothing in this lesson requires bigfile tablespaces, but it's useful vocabulary for when you see it in Oracle's own documentation or in larger production environments.

Tablespaces are created using the CREATE TABLESPACE statement. A basic version of the command looks like this:

CREATE TABLESPACE tablespace_name
  DATAFILE 'full_path/filename.dbf' SIZE 5M;

This simplified syntax is fine for small-scale or test environments. In practice, you should carefully determine file locations, names, sizes, and whether the datafiles should support autoextend.

• Guidelines for File Placement
  OFA recommends placing tablespace datafiles on separate physical disks or storage volumes to distribute I/O activity evenly and prevent contention.
  • Temporary tablespace: heavily used during sorting and complex joins — place it on a dedicated drive to minimize I/O contention with other tablespaces' datafiles.
  • Undo tablespace: records undo data during every transaction. Place it on separate storage from user datafiles to keep write latency down during updates.
  • Users and Tools tablespaces: generally lightly accessed in most installations. Sharing a disk between them, or with other low-activity tablespaces, is typically fine outside of production.

Initial Configuration Parameters
For demonstration or development purposes, this configuration is a reasonable starting point:

Tablespace Name	Datafile Name	Initial Size
USERS	users01.dbf	5 MB
TOOLS	tools01.dbf	5 MB
TEMP	temp01.dbf	5 MB
UNDO	undo01.dbf	5 MB

Considerations for Sizing: The appropriate size for each tablespace depends on application data volume, the number of concurrent users, and any installed tools that store metadata in the database. Treat these values as initial estimates, and adjust as system demands evolve.

The sizing of initial tablespaces depends on their intended use and the volume of data the database will handle. Each tablespace serves a distinct role within the Oracle architecture and should be sized accordingly.

• USERS: Typically the default tablespace for application-level accounts. Keep it relatively small unless a specific application schema needs more space — large-scale applications are usually better served by dedicated application tablespaces. Expansion is easy: add datafiles as needed.
• TOOLS: Supports third-party or Oracle-provided database tools, when used. Sizing depends on the specific tool — consult its installation documentation. Many environments create a separate tablespace per tool to keep storage modular. Like other tablespaces, TOOLS can be extended dynamically.
• TEMP: Essential for sorts, hash joins, and global temporary table usage. When an operation exceeds available memory, Oracle writes temporary segments to disk here. Size it to reflect expected concurrent sorting and temporary-processing demand, and monitor it in systems that handle frequent large operations.
• UNDO: Holds undo data for transaction rollback, read consistency, and flashback operations. Oracle AI Database uses automatic undo management, which simplifies configuration considerably — you size the undo tablespace, and Oracle manages the segments within it. Sizing depends on the number and size of concurrent transactions; monitor undo usage and retention settings for optimal performance. Remember, too, that with local undo mode (the default), this sizing question applies separately to the CDB root and to each PDB.

Now, try this exercise to create tablespaces for your database.
Create Initial Tablespaces - Exercise

[1]OFA-compliant paths: OFA-compliant paths in Oracle AI Database (and generally in Oracle installations) refer to the Optimal Flexible Architecture guidelines. OFA is a set of configuration rules and conventions designed by Oracle to provide a well-organized, scalable, and manageable database environment.