Bigfile Tablespace Defaults in Oracle Database 23ai

Bigfile tablespaces were introduced in Oracle 10g. In Oracle database 23ai bigfile tablespaces are the default.

◉ BIGFILE Default for SYSAUX, SYSTEM, and USER Tablespaces

In Oracle database 23ai most of the Oracle provided tablespaces are bigfile tablespaces by default.

We connect to the root container and check out the tablespaces for this container. All are bigfile tablespaces excluding the TEMP tablespace.

conn / as sysdba

select tablespace_name, bigfile

from   dba_tablespaces

order by 1;

TABLESPACE_NAME      BIG

------------------------------  ---

SYSAUX                         YES

SYSTEM                         YES

TEMP                              NO

UNDOTBS1                    YES

USERS                           YES

SQL>

We switch to a pluggable database and display the tablespaces for this container. This is similar, but the USERS tablespace in the PDB is a smallfile tablespace. This is the default behaviour in this release of 23ai Free.

alter session set container=freepdb1;

select tablespace_name, bigfile

from   dba_tablespaces

order by 1;

TABLESPACE_NAME   BIG

------------------------------ ---

SYSAUX                         YES

SYSTEM                         YES

TEMP                              NO

UNDOTBS1                   YES

USERS                           NO

SQL>

◉ BIGFILE Default for User Defined Tablespaces

Bigfile is the default file size when creating a new tablespace. In the following example we create a new tablespace without explicitly setting the bigfile/smallfile file size. We are using Oracle Managed Files (OMF), so we don't need to manually name the associated datafile.

create tablespace new_ts datafile size 2g;

Notice the new tablespace has been created as a bigfile tablespace.

select tablespace_name, bigfile

from   dba_tablespaces

order by 1;

TABLESPACE_NAME    BIG

------------------------------ ---

NEW_TS                         YES

SYSAUX                         YES

SYSTEM                         YES

TEMP                               NO

UNDOTBS1                    YES

USERS                            NO

SQL>

◉ Considerations

These new defaults were introduced in 23.4. if I run the same tests in 23.3 I get the following.

conn / as sysdba

select tablespace_name, bigfile

from   dba_tablespaces

order by 1;

TABLESPACE_NAME   BIG

------------------------------ ---

SYSAUX                         NO

SYSTEM                         NO

TEMP                              NO

UNDOTBS1                    NO

USERS                            NO

SQL>

alter session set container=pdb1;

select tablespace_name, bigfile

from   dba_tablespaces

order by 1;

TABLESPACE_NAME   BIG

------------------------------ ---

SYSAUX                         NO

SYSTEM                         NO

TEMP                              NO

UNDOTBS1                    NO

USERS                            NO

6 rows selected.

SQL>

create tablespace new_ts datafile size 2g;

select tablespace_name, bigfile

from   dba_tablespaces

order by 1;

TABLESPACE_NAME  BIG

------------------------------ ---

NEW_TS                         NO

SYSAUX                         NO

SYSTEM                         NO

TEMP                              NO

UNDOTBS1                   NO

USERS                            NO

SQL>

I've been told some of the 23ai cloud services have different defaults in this behaviour. I suspect this is because of different versions of the software at the point of the image creation they are based on. I would expect future release updates to result in all tablespaces, including TEMP, to be bigfile when a clean installation is performed.

Source: oracle-base.com