Oracle 23ai Database : BIGFILE Default for SYSAUX, SYSTEM, and USER Tablespaces

In ORACLE 23ai Database, BIGFILE is the DEFAULT for most of the Oracle Provided Tablespaces like SYSAUX, SYSTEM and USER Tablespaces (Except Temp Tablespace)

eg: Lets Connect and Verify

sql>conn / as sysdba

sql>select tablespace_name, bigfile from  dba_tablespaces order by 1;

TABLESPACE_NAME              BIGFILE
---------------------------- ---
SYSAUX                       YES
SYSTEM                       YES
TEMP                         NO
UNDOTBS1                     YES
USERS                        YES

If we verify connecting Pluggable Databases ptmpdb

sql>alter session set container=ptmpdb;

or 

using tns net service name

sql>conn username/passwd@ptmpdb

sql>select tablespace_name, bigfile from   dba_tablespaces order by 1;

TABLESPACE_NAME                BIGFILE
------------------------------ ---
SYSAUX                         YES
SYSTEM                         YES
TEMP                           NO
UNDOTBS1                       YES
USERS                          NO

Creating User Defined Tablespace is Default BIGFILE

Using OMF concept , we don’t need to specify the datafile name and location as we can find the location from parameter db_create_file_dest.

Here we create tablespace with name ptmtbs.

sql>create tablespace ptmtbs datafile size 4g;

sql>select tablespace_name, bigfile from   dba_tablespaces order by 1;

TABLESPACE_NAME                BIGFILE
------------------------------ ---
PTMTBS                         YES
SYSAUX                         YES
SYSTEM                         YES
TEMP                           NO
UNDOTBS1                       YES
USERS                          NO