Creating initial Tablespaces in Oracle 23c

To create an initial tablespace in Oracle 23c, especially when creating a custom database or following Optimal Flexible Architecture (OFA) guidelines, 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

✅ Basic Example: Create a `USERS` Tablespace in Oracle 23c

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

🔍 Explanation of Key Clauses

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	Required in Oracle 23c (uses bitmap for space management)
SEGMENT SPACE MANAGEMENT AUTO	Uses automatic free space management for better performance

1. Use Locally Managed Tablespaces only (EXTENT MANAGEMENT LOCAL).
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.
5. Separate UNDO and TEMP tablespaces during database creation.

🔄 Creating Other System Tablespaces (during DB creation)
While creating the full database, you would also define:

• SYSTEM – holds data dictionary and PL/SQL packages
• SYSAUX – auxiliary system tablespace (e.g., AWR, OEM)
• UNDO – for undo segments
• TEMP – for temporary operations

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

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

The following is a `CREATE DATABASE` script for Oracle 23c, including all required tablespaces and system options, following OFA-compliant paths^[1] and best practices:
✅ Full

STARTUP NOMOUNT
  PFILE='/u01/app/oracle/product/23c/dbs/initORCL23C.ora';

CREATE DATABASE ORCL23C
  USER SYS IDENTIFIED BY SysPassword#2025
  USER SYSTEM IDENTIFIED BY SystemPassword#2025
  LOGFILE GROUP 1 ('/u01/app/oracle/oradata/ORCL23C/redo01.log') SIZE 100M,
          GROUP 2 ('/u01/app/oracle/oradata/ORCL23C/redo02.log') SIZE 100M,
          GROUP 3 ('/u01/app/oracle/oradata/ORCL23C/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/ORCL23C/system01.dbf' SIZE 700M
    AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
  SYSAUX DATAFILE '/u01/app/oracle/oradata/ORCL23C/sysaux01.dbf' SIZE 550M
    AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
  DEFAULT TABLESPACE users
    DATAFILE '/u01/app/oracle/oradata/ORCL23C/users01.dbf' SIZE 100M
    AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
  DEFAULT TEMPORARY TABLESPACE temp
    TEMPFILE '/u01/app/oracle/oradata/ORCL23C/temp01.dbf' SIZE 100M
    AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
  UNDO TABLESPACE undotbs1
    DATAFILE '/u01/app/oracle/oradata/ORCL23C/undotbs01.dbf' SIZE 200M
    AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED;

🔧 Next Steps After Database Creation
After running the `CREATE DATABASE` command, you need to:

• Run Oracle-supplied scripts to create data dictionary and PL/SQL packages:

  @?/rdbms/admin/catalog.sql
  @?/rdbms/admin/catproc.sql
  @?/sqlplus/admin/pupbld.sql
  

• Configure Listener (optional) via NETCA or manually in listener.ora.
• Create spfile (if not already done):

  CREATE SPFILE FROM PFILE='/u01/app/oracle/product/23c/dbs/initORCL23C.ora';
  

📌 Notes:Important Considerations for Oracle Database Initialization

• Adjust file sizes and paths according to your environment.
• Always validate your `initORCL23C.ora` before using it in `STARTUP NOMOUNT`.
• You can optionally define additional tablespaces like `TOOLS` or application-specific ones later.

An Oracle database structured according to the Optimal Flexible Architecture (OFA) model should include a foundational set of tablespaces to ensure modularity, scalability, and ease of maintenance. The following tablespaces are commonly established in OFA-compliant environments:

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

Tablespaces are created using the `CREATE TABLESPACE` statement. A basic version of the command appears below:

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

This simplified syntax is suitable 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: As this tablespace is heavily utilized during operations involving sorting or complex joins, it should reside on a dedicated drive to minimize I/O contention with datafiles from other tablespaces.
  • Undo tablespace: This tablespace records undo data during transactions. To ensure efficient write performance and minimize latency during updates, place the undo tablespace on a separate storage device from user datafiles.
  • Users and Tools tablespaces: These are generally lightly accessed in many installations. As such, placing them on the same disk as each other or with other low-activity tablespaces is typically acceptable in non-production environments.

Initial Configuration Parameters
For demonstration or development purposes, the following configuration can be used as a 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 of each tablespace depends on the volume of application data, the number of concurrent users, and any installed tools that store metadata in the database. These values should be treated as initial estimates and adjusted 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 The USERS tablespace is typically designated for user-created objects. In many environments, it serves as the default tablespace assigned to application-level accounts. It is advisable to keep this tablespace relatively small unless specific application schemas require more space. Objects related to large-scale applications are often better placed in dedicated application tablespaces. Expansion can be managed easily by adding datafiles as needed.
• TOOLS: The TOOLS tablespace, when used, supports third-party or Oracle-provided database tools. The sizing depends on the particular software being installed. It is recommended to consult the installation documentation for the specific tool to determine tablespace requirements. In many modern environments, administrators create separate tablespaces for each tool to maintain modular storage structures. Like other tablespaces, TOOLS can be extended dynamically when required.
• TEMP: The TEMP tablespace is essential for operations involving temporary data such as sorts, hash joins, and global temporary table usage. During large data operations that exceed memory limits, Oracle writes temporary segments to disk within this tablespace. Its size should reflect the expected volume of concurrent sorting and temporary processing demands. Monitoring and resizing may be necessary in systems handling frequent large operations.
• UNDO: The UNDO tablespace holds undo data used for transaction rollback, read consistency, and flashback operations. Proper sizing of UNDO depends on the number and size of concurrent transactions. Unlike older architectures, Oracle 23c uses automatic undo management, which simplifies configuration and relies on system-managed undo segments stored in this tablespace. Monitoring undo usage and retention settings is important to ensure optimal performance.

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

[1]OFA-compliant paths: OFA-compliant paths in Oracle 23c (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.