Oracle 26ai Tablespaces Default to Bigfile? Verification and Precautions

In Oracle Database 26ai (version 23.26.1 and later), the latest release for 2026, Oracle 26ai tablespaces are configured as Bigfile by default during database creation. While traditional Oracle Bigfile tablespaces were previously recommended only for large-scale data, the default behavior has changed in 26ai. This can lead to issues if operations are conducted assuming traditional Smallfile management. This article explains how to verify the latest specifications in version 23.26.

Conclusion and Quick Steps
Background and Fundamentals: Changes in 26ai (23.26)
1. Quick Note for Beginners
Implementation: Determining Default Tablespace Type
1. Execution Example: Verification Log in 26ai (23.26)
Demonstration: Explicitly Creating Smallfile Tablespaces
Troubleshooting: Common ORA Errors
Operational, Monitoring, and Security Notes
FAQ (Frequently Asked Questions)
Summary

Conclusion and Quick Steps

In Oracle 26ai, the following steps allow you to prevent unintended Bigfile tablespace creation or verify current settings:

Verify current attributes: Check the BIGFILE column in CDB_TABLESPACES (YES indicates Bigfile).
Capacity expansion practice: Use ALTER TABLESPACE ... RESIZE instead of ADD DATAFILE.
Creation with Smallfile specification: Explicitly use CREATE SMALLFILE TABLESPACE ... during creation.
Permanent change of default: Execute ALTER DATABASE SET DEFAULT TABLESPACE TYPE SMALLFILE;.

Background and Fundamentals: Changes in 26ai (23.26)

Oracle Database has two types of management methods for datafiles:

Smallfile tablespace (Traditional): Composed of multiple datafiles (up to 1,022). Each file is limited to approximately 32GB.
Bigfile tablespace (26ai Standard): Composed of a single massive datafile. Can extend up to 128TB.

In the latest 26ai (23.26), not only user tablespaces (USERS) but even system tablespaces (SYSTEM/SYSAUX) are provisioned as Bigfile by default to optimize operational efficiency and cloud-native configurations.

Quick Note for Beginners

The 20-year-old habit of “adding files to increase capacity” no longer applies in 26ai. The 26ai way is to “resize existing files.”

Implementation: Determining Default Tablespace Type

We will verify the configuration immediately after installation on a physical machine (Oracle Linux / Oracle Database 23.26.1).

Log in via sqlplus / as sysdba.
Use the following SQL to check tablespace types in bulk.
Evaluate the YES/NO status in the BIGFILE column.

Tablespace Name	19c / 21c	26ai (23.26)	Operational Constraints
SYSTEM	Smallfile	Bigfile (YES)	Cannot add files (Resize only)
SYSAUX	Smallfile	Bigfile (YES)	Cannot add files
USERS	Smallfile	Bigfile (YES)	Cannot add files
TEMP	Smallfile	Smallfile (NO)	Temporary tablespaces often remain traditional

Execution Example: Verification Log in 26ai (23.26)

Verification results on a physical machine confirm that all major tablespaces are set to BIGFILE=YES.

-- Privileges: SYSDBA, Environment: CDB/PDB configuration
-- Verifying execution results in 23.26.1.0.0
SELECT 
    TABLESPACE_NAME, 
    BIGFILE, 
    CON_ID 
FROM 
    CDB_TABLESPACES 
ORDER BY 
    CON_ID, TABLESPACE_NAME;

Explanation of execution results:

SQL> SELECT
  2      TABLESPACE_NAME,
  3      BIGFILE,
  4      CON_ID
  5  FROM
  6      CDB_TABLESPACES
  7  ORDER BY
  8      CON_ID, TABLESPACE_NAME;

TABLESPACE_NAME                BIG     CON_ID
------------------------------ --- ----------
SYSAUX                         YES          1
SYSTEM                         YES          1
TEMP                           NO           1
UNDOTBS1                       YES          1
USERS                          YES          1
APP_DATA                       NO           3
SYSAUX                         YES          3
SYSTEM                         YES          3
TEMP                           NO           3
UNDOTBS1                       YES          3
USERS                          YES          3

11 rows selected.

[Explanation] All entries where the BIG column displays YES are Bigfile tablespaces. The traditional ALTER TABLESPACE ... ADD DATAFILE command will result in an error.

Demonstration: Explicitly Creating Smallfile Tablespaces

If you wish to manage multiple datafiles as in traditional versions, regardless of the 26ai default setting (Bigfile), append the SMALLFILE keyword immediately after the CREATE statement.

Example of creating a Smallfile tablespace on the PDB side (PDB01):

-- Switch to PDB
ALTER SESSION SET CONTAINER = PDB01;

-- Create by explicitly specifying the SMALLFILE keyword
CREATE SMALLFILE TABLESPACE APP_DATA
DATAFILE '/u01/app/oracle/oradata/ORCL/pdb01/app_data01.dbf' SIZE 100M
AUTOEXTEND ON;

-- Verification query
SELECT TABLESPACE_NAME, BIGFILE FROM USER_TABLESPACES WHERE TABLESPACE_NAME = 'APP_DATA';
-- Result: BIGFILE will be "NO"

[Explanation] Since 26ai defaults to BIGFILE unless specified otherwise, this designation is mandatory if your management requirements necessitate separate files.

SQL> ALTER SESSION SET CONTAINER = PDB01;

Session altered.

SQL> CREATE SMALLFILE TABLESPACE APP_DATA
  2  DATAFILE '/u01/app/oracle/oradata/ORCL/pdb01/app_data01.dbf' SIZE 100M
  3  AUTOEXTEND ON;

Tablespace created.

SQL> SELECT TABLESPACE_NAME, BIGFILE FROM USER_TABLESPACES WHERE TABLESPACE_NAME = 'APP_DATA';

TABLESPACE_NAME                BIG
------------------------------ ---
APP_DATA                       NO

Troubleshooting: Common ORA Errors

Attempting traditional “add file” operations in 26ai will result in the following errors:

Error Code	Cause	Solution
ORA-32771	Attempted to add a file to a Bigfile tablespace	Use the `RESIZE` command
ORA-01144	File exceeded OS limit (or 128TB)	To split files, you must split tablespaces

Error Reproduction Example:

SQL> ALTER TABLESPACE USERS ADD DATAFILE '/u01/app/oracle/oradata/ORCL/users02.dbf' SIZE 1G;
ALTER TABLESPACE USERS ADD DATAFILE '/u01/app/oracle/oradata/ORCL/users02.dbf' SIZE 1G
*
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace
Help: https://docs.oracle.com/error-help/db/ora-32771/

Operational, Monitoring, and Security Notes

Pros: The number of datafiles is drastically reduced, preventing control file bloat and decreasing checkpoint processing overhead.
Cons: If a single file reaches several TB, copying or moving it at the file system level takes time.
How to revert: If Smallfile is absolutely required, execute the following command immediately after database creation:

ALTER DATABASE SET DEFAULT TABLESPACE TYPE SMALLFILE;

FAQ (Frequently Asked Questions)

Q: Why is Bigfile the default in 26ai? A: In storage virtualization and cloud environments (such as OCI), treating data as a single large volume is more efficient for management and performance than splitting files into small pieces.

Q: Can I convert a Bigfile tablespace back to Smallfile later? A: Direct conversion is impossible. You must export the data (Data Pump), create a Smallfile tablespace, and import the data into it.

Q: Does this affect backup times? A: When using RMAN, you can use the SECTION SIZE parameter to back up a single giant file in parallel, preventing significant delays.

Summary

In Oracle Database 26ai (23.26), major tablespaces are Bigfile by default.
Address capacity shortages using ALTER DATABASE DATAFILE ... RESIZE.
Existing monitoring scripts using ADD DATAFILE must be modified.
If compatibility is a priority, change the default type during database creation.

[reference]
BIGFILE Default for SYSAUX, SYSTEM, and USER Tablespaces

This article targets Oracle Database 26ai (23.26.1). (Screenshots and default values may differ in other versions.)