Exploring Bigfile Tablespaces with Default Settings in oracle 23ai

BIGFILE Tablespace

A bigfile tablespace is a tablespace with a single, but large datafile. Traditional small file tablespaces, in contrast, typically contain multiple datafiles, but the files cannot be as large. Making SYSUAX, SYSTEM and USER tablespaces bigfile by default will benefit large databases by reducing the number of datafiles, thereby simplifying datafile, tablespace and overall global database management for users.

Default Bigfile Tablespace

By default, when we create a tablespace in Oracle 23ai, it will be a Bigfile Tablespace.

Now, if we want to create a Smallfile Tablespace, we can change the default to Smallfile Tablespace using the command

ALTER DATABASE SET DEFAULT SMALLFILE TABLESPACE;

The following is an example:

Alternatively, you can use the SMALLFILE keyword in the CREATE TABLESPACE command.

CREATE SMALLFILE TABLESPACE amir_smallfile DATAFILE '/opt/oracle/oradata/FREE/FREEPDB1/amir_smallfile_1.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

NOTE1: To change the default back to Bigfile Tablespace, use the following command:

ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;

NOTE2: Starting with Oracle Database 23ai, BIGFILE functionality is the default for SYSAUX, SYSTEM, and USER tablespaces.