BIGFILE Tablespace
A BigFile tablespace is a tablespace with a single, but large datafile. Traditional small file tablespaces, in contrast, typically contain multiple data files, 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, 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.